Forum Discussion
Xlookup shifts if I add a column to the query
Are you using proper column names? If the 4th column in the final PQ loaded table is called, for instance, "Reference", then it doesn't matter if it will in the 2nd or 10th column after a refresh. As long as it's called "Reference", XLOOKUP will find it.
Also tried 'inserting' by deleting a "removed column" step. No problem. Perhaps I just don't understand what you mean. Sorry.
Yes I’m using proper column names, I just used numbers to help understand the order but that’s is exactly what’s happening. If my column names are, Apple, Banana, cranberry, if my XLOOKUP is referencing “Cranberry” (the 3rd one) but if my column order is now Apple, GRAPE, banana, cranberry, my reference automatically switches to “Banana” (the 3rd one). Is there a setting I accidentally switched? Literally everyone’s response online is “that’s shouldn’t happen” but it is happening and I’m gonna lose my mind . It’s also effecting FILTER commands and anything that’s is referenced like “Table_Name[Column Name]”, they all shift over.
References within a table are properly aligned but anything that’s a cross reference get shifted over by the number of new columns there are.
Another example I typed up for Microsoft support.
My table name is "Tracking_Table" and in order, my columns are named
Client Name, Location, Ratio, Notes.
In another table I have
=XLOOKUP("John Smith", Tracking_Table[Client Name], Tracking_Table[Notes],"").
And then in power query, a new column is added in between Location and Ratio called Edits. My new order of columns is Client Name, Location, Edits, Ratio, Notes.
When the query refreshes, my formula changes to =XLOOKUP("John Smith", Tracking_Table[Client Name], Tracking_Table[Ratio],"")
This is just one of the many examples in my workbook. Anything to the right of the new columns will be offset and because there are so many files and so many lookups, there is no way to just put all new columns to the right because there will always be more columns to the right.
