Forum Discussion
Issue with power query
cng77 You can't just add a column to a PQ generated table and expect it to stay in sync when you extend the raw data. There are special techniques for this.
Google for "self referencing table power query" and you get many sites that deal with this. The principle is to connect to the initial source, load it into Excel and add that loaded table as a second source to the query. Add your extra column(s) to the loaded table. Then, merge Source with Source2 (hopefully you have a unique ID to do that by). Then, the manually added information will stay synced with the original source data when you refresh it.
Sounds intimidating, but the tutorials you find online are quite clear.
cng77 You can't just add a column to a PQ generated table and expect it to stay in sync when you extend the raw data. There are special techniques for this.
Google for "self referencing table power query" and you get many sites that deal with this. The principle is to connect to the initial source, load it into Excel and add that loaded table as a second source to the query. Add your extra column(s) to the loaded table. Then, merge Source with Source2 (hopefully you have a unique ID to do that by). Then, the manually added information will stay synced with the original source data when you refresh it.
Sounds intimidating, but the tutorials you find online are quite clear.
- Thank you very much again, I have that working - the manually added columns are now at the end of the query, after their self referencing counterparts- can I delete those manual columns now?
And one more question, if you can, some of those columns had formulas (financial subtotals) - if I enter them in the self referencing columns, will they stay or are additional steps needed for that?cng77 PQ doesn't retain formulas, but perhaps you can add the formula a sa custom column within PQ.
- Thank you for this! Reading up on it now. I mistakenly believed that since sorting stayed in sync on the file when opened, it would do the same when data refreshes. Thank you again.
