Tables - refer to range with specific column title

[sauzee]

Hi all - I have a table linked to external data. Unfortunately, the order of the columns change often when new columns are added/delete.

I'm using table references, for example "table1[date]" to refer to the date column. I thought that this reference would update to wherever the date column is moved to. But it basically is still a cell reference. For example, if date is in column A, it will always refer to column A, even if the date column moved to B.

Is there any easy to move these references as a column moves? It seems like an obvious missing feature and not much of an improvement of cell references other than making it more legible.

I understand I could use INDEX/MATCH or INDIRECT to find the column title but I'm working with quite a big dataset that is already quite slow and don't think it could cope with these formulas (lots of averageifs!). I'm guessing there's probably a way to "freeze" the column order in power query but I'm curious to find whether there's an easy way (such as an add-in) that anyone has discovered or knows about?

Thanks in advance

 

[Fluff]

When using structured references like Table1[Date] in a formula, the formula will always look at the Date column regardless of where it's moved to.

 

[sauzee]

Hi Fluff- thanks for the reply. Yes that would be the way I would expect it to work. However, it seems that only happens when the table is edited within excel. For example, if I delete a column from a table in excel, the table reference will continue to lookup the correct column. However, it seems if the data comes from an external data source, such as a web query or a sql database, then the table reference will edit to whatever the new column title is within that excel cell reference (e.g. column B). I'm guessing this is a bug then

 

[Fluff]

I've never dealt with data coming from external sources, so cannot help with that.
Hopefully another member will be able to offer help.

 

[RoryA]

It is annoying. Usually the simplest solution is to right-click the table, choose external data properties, and check the option to 'preserve column sort/filter/layout' - assuming you don't mind the table order being preserved even if you reorder the columns in PQ.

 

[sauzee]

Hi Rory - yes that works thanks- simple!