Updating a table from a power query changes formula reference

Question

Saw this was previously asked here on SO and had no solution and I can't find a solution through Google either.

I have a workbook with a tab called "Data" containing a table which is updated via a power query and another tab called "Calcs" with formulas referencing the cells in the table from "Data". When I refresh the table, it pulls data via the power query, but when it's done, the formula references change.

For example, before the refresh, I'll have formulas like this in the "Calcs" tab:

=COUNTIFS('Data'!$A$2:$A$26886,$A1060,'Data'!$K$2:$K$26886,'BY CAT'!$B1060)

After the refresh, the references for column A only change to

=COUNTIFS('Data'!$A$10242:$A$26886,$A1060,'Data'!$K$2:$K$26886,'BY CAT'!$B1060)

And it results ina #VALUE! error message.

How can I prevent Excel from creating this reference shift?

Answer 1

You might be able to get around this by using full column references:

=COUNTIFS('Data'!$A:$A,$A1060,'Data'!$K:$K,'BY CAT'!$B1060)

Full column references aren't always a good idea, but it might just work in this case.

Edit:

Table column references would be ideal, TableName[ColumnName]. These should work given that you are reading from a power query generated table.