Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

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

  1. #1
    Neophyte h5th's Avatar
    Join Date
    Jan 2022
    Posts
    2
    Articles
    0
    Excel Version
    365

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Click image for larger version. 

Name:	Screenshot 2022-01-16 134457.png 
Views:	13 
Size:	21.4 KB 
ID:	10790
    Click image for larger version. 

Name:	Screenshot 2022-01-16 134530.png 
Views:	16 
Size:	24.0 KB 
ID:	10791Click image for larger version. 

Name:	Screenshot 2022-01-16 134604.png 
Views:	15 
Size:	22.9 KB 
ID:	10792

    Then i started a new file and get data from power query to combine the file with the same column name like this :
    Click image for larger version. 

Name:	Screenshot 2022-01-16 134654.png 
Views:	15 
Size:	44.5 KB 
ID:	10793

    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:
    Click image for larger version. 

Name:	Screenshot 2022-01-16 134743.png 
Views:	16 
Size:	51.1 KB 
ID:	10794
    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,493
    Articles
    43
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    177
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by Ken Puls View Post
    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.
    Attached Files Attached Files

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    Did you add all of the English translations into that workbook just for us? That's going above and beyond man

  5. #5
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    177
    Articles
    0
    Excel Version
    Office 365
    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.

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    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.

  7. #7
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    177
    Articles
    0
    Excel Version
    Office 365
    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.
    Attached Files Attached Files

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,493
    Articles
    43
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by pinarello View Post
    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. 😉
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by pinarello View Post
    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.

  10. #10
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    177
    Articles
    0
    Excel Version
    Office 365
    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.

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •