Forum Discussion

cng77's avatar
cng77
Copper Contributor
Jan 11, 2023
Solved

Issue with power query

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...
excel
power bi
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jan 11, 2023

    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's avatar
cng77
Copper Contributor
Jan 11, 2023
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?
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jan 11, 2023

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

Resources