Excel formulas don't follow after adding a column to powerbi query

[markluky]

Hi community,

I have a powerquery table that aggregates date from multiple source files and pastes the resulting table in an excel spreadsheet. Then, I have a dashboards and calculations that link to that table and works properly.

The issue: When I add a new column to all the source files (same for all files), the excel calculations do not correctly follow (unlike if you simply insert a column in Excel). I thought the solution was simply to add a column to the table (in the same position) before overwriting the table with the new query, but actually it keeps the manually added column and then adds also the new column from the data files, which means my formulas all get messed up again.

Is there a mechanism in which I can add a column to the source files and the resulting table, and my excel formulae, that link to the resulting table, adapt dynamically? I know changing all my formulas to INDEX/MATCH rather than XLOOKUP would help but these formulae are to complicated for my clients to manage in the future.

Not able to share the file because company private data. Thank you very much.

[markluky]

I didn't know we could call queries values that way. I tried it and it worked! Thank you so much.

[BA_Pete · Accepted solution]

Hi @markluky ,

Not sure I follow what the issue is exactly.

Are your Excel formulas in the following format?

= queryName[@queryColumnName1] + queryName[@queryColumnName2]

If they are, then there should be no issue with the Excel formulas as they reference actual field names. Excel doesn't care what columns you have, as long as the table contains the one(s) it's looking for.

Pete