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
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