Data changes on original files after changing data on the power query report

[h5th]

Hello, i am wondering if i can changes the source data if i made changes to the power query reports. Here's the case scenario:
I have multiple workbook in one folder, i have formatted all the workbook with the same table name



Then i started a new file and get data from power query to combine the file with the same column name like this :


But my question is, is there any way if i made changes to the power query report in file pq.xlsx, so the date on the source file will also be changed?
for example here:

i made changes on B6 and C6 which are the data on file Data2.xlsx. I wonder is it possible that the data on Data2.xlsx changes automatically after i made such changes?
If it's not possible with power query, is there any tools to make this work? Thank you.

[Ken Puls]

Not with Power Query. Power Query can only read from a data source, not write back to the data source.
If you need writeback, the best suggestion I would have is to use VBA. But it will take a custom solution that would take some time to code, as you'd need to define the rules first, then the refresh and write back triggers/methods.

[pinarello]

Not with Power Query. Power Query can only read from a data source, not write back to the data source.
If you need writeback, the best suggestion I would have is to use VBA. But it will take a custom solution that would take some time to code, as you'd need to define the rules first, then the refresh and write back triggers/methods.

Hello Ken,
if the source is not in the current workbook, you are correct. But if the source is inside the current workbook, then after creating a query, you can assign the table you just created in the query as the source.

Some time ago, based on a request, I created an application in the German ms-office-forum.net, which can be used to manage the rental of equipment within the company. Here it is so that I see the current status in the spreadsheet "Übersicht" (Overview) and also directly perform bookings.

To simplify the handling, the queries are triggered by some VBA code.

In the example workbook the devices are marked "green", which can be borrowed and "red", if the end date is overdue.

I have described the handling on the "Instructions" spreadsheet.

[Bob Phillips]

Did you add all of the English translations into that workbook just for us? That's going above and beyond man

[pinarello]

Knowledge lives from the fact that knowledge is shared. And if I share something, or make it easier for others to understand my solution, then I will also benefit from it in the end.

[Bob Phillips]

I was very disappointed that you didn't translate the VBA comments though

BTW do you use the moniker pinarello in tribute to the Italian bike maker? I have a pinarello in the garage.

[pinarello]

Sorry, that was not intentional. I had simply neglected to take another look at the VBA code as well.

But now I have also translated the comments of the VBA code. These comments were very important for me, , because I had to google a lot for the code.


Yes, from 1983 to 1989, my 2nd road bike, I rode a Pinarello. Since I had afforded myself the luxury of having the frame made according to my body measurements. Unfortunately, then a garage, which I had to visit on the way to a training camp in Italy, damaged the frame. From a speed of about 65km (40 miles) the frame has then started to flutter. The next 15 years I then drove Raleigh. Since I had quite by chance the luck to have gotten a professional frame. Then a few years Colnage and since the end of 2007 I drive with a Look Ultra 585 a carbon frame.

But I was always just a hobby rider, even if there were years in which I rode 10,000 kilometers and sometimes for 200 kilometers only needed 7 hours.

[Ken Puls]

Hello Ken,
if the source is not in the current workbook, you are correct. But if the source is inside the current workbook, then after creating a query, you can assign the table you just created in the query as the source.

Ah, quite true pinarello. I generally avoid circular reference queries like this out of principal. 😉

[Bob Phillips]

Sorry, that was not intentional. I had simply neglected to take another look at the VBA code as well.

But now I have also translated the comments of the VBA code. These comments were very important for me, , because I had to google a lot for the code.

That was a joke pinarello, I didn't really expect you to. Anyway, I never write comments myself, and rarely read then :-(.


My bike is a pinarello stelvio with a campagnolo groupset. I bought the frame and had my local bike shop make it up for me. I must admit I have never gotten on with carbon, I like the solidity of steel. My pinarello is quite old now, but it still rides well. I am far too old to be anything other than a hobby rider now.

[pinarello]

Even if I didn't recognize the "joke", it didn't bother me to translate the few comments. Whereby I usually comment rather little, because at the time of programming everything is so clear as daylight for me. But I have also caught myself that years later I could not remember why I programmed the way I find it now.

At some point I had the feeling that there were only aluminum and carbon frames. With steel I was always very satisfied, but aluminum I did not want to drive, because too hard. That's why I then decided on carbon. And a road-ready road bike with less than 7 kilos is probably not achievable with steel.