Issue with power query

[cng77]

Hi everyone, might be a silly question..

I have a table power queried into another sheet, and then I have expanded to the table to include original data based on that. I am finding that the original data is not saving properly - I entered it in one row, but when I open and refresh the data it has moved randomly to another row.

Basically the queried data row is addresses, the original data finances. So I've entered $25 under 123 Blue Street, but when I go back to the file later, that $25 is under a totally different address/row. What am I doing wrong here?

[#2 · Accepted solution]

@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.

[#3]

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.

[#4]

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?

[#5]

@cng77 PQ doesn't retain formulas, but perhaps you can add the formula a sa custom column within PQ.