Tablecolumn reference in Excel formula changes at new column in table after refresh

0
votes

I have a formula like this:

=INDEX(tblProduct[Supplier];MATCH([@ProductID];tblProduct[ProductID];0))

Here "tblProduct" refers to a range that contains a query result. Source of that data is a table in another Excel workbook. It has three columns:

  1. ProductID
  2. ProductName
  3. Supplier

Now when I add a column before Supplier, e.g. "IntroductionDate" and I refresh the data in the 'slave' workbook, above mentioned formulas changes into:

=INDEX(tblProduct[IntroductionDate];MATCH([@ProductID];tblProduct[ProductID];0))

How can I prevent this? I know I can prevent this by setting the column order in the query in the 'slave' workbook, but that's in my opinion a workaround.

So is there a way to make a 'hard' reference to 'tblProduct[Supplier'?

1
excel-formularefreshpowerquery

1 Answers

0
votes

I realise you've asked this a few weeks back and may no longer need an answer, but will respond anyway in case it helps someone else.

I'm not aware of any feature that would allow you to create an absolute column reference in the manner you've described. (Would be great if such behaviour could be made user-specifiable, since there will probably also be use cases where the current behaviour is desired).

What I currently do is replace:

tblProduct[Supplier]

with:

INDEX(tblProduct,0,MATCH("Supplier",tblProduct[#Headers],0))

(Based on the formula in your question, you may need to replace , with ; -- but should work otherwise.)

A benefit of this approach is that it will always attempt to look up the Supplier column (and return all rows from it) -- so it is "absolute" in that respect. A drawback is that it complicates the formula (which can be an issue if your formula is already long) and I've not really tested if there is any sort of performance hit with this approach.

Another approach might involve using INDIRECT("tblProduct[Supplier]") instead. The benefit of this approach is that it seems to achieve the same thing. But drawbacks include INDIRECT being a volatile function (so will re-calculate every single time, which can worsen spreadsheet performance) and the table's name in the formula won't update automatically if you rename the table.

You may consider these to be workarounds too (compared to what you had in mind).