Forum Discussion
Xlookup shifts if I add a column to the query
I have a query table that I'm referencing using an xlookup function on another table. When I add a column to the query, any xlookup gets shifted over one column.
=XLOOKUP ("Value", Querytable[Column1],Querytable[Column4],"") but if I add a column in between 2 and 3 in the power query editor, when the table load my formula changes to =XLOOKUP ("Value", Querytable[Column1],Querytable[Column5],""). I thought doing formulas this way was supposed to prevent that.
If I add a column to a table by right click and inserting a column things work fine and my formulas track with the proper column, but when its its added in power query it doesn't work and I can't fix 100+ formulas each time a column is added to a query that pulling data from hundreds of files, columns being added is inevitable.
10 Replies
Whatever. I never use Excel for the web. It's just not the same as real Excel. Still too many quirks for my taste. Even though it's getting better all the time.
Most probably "Preserve column sort/filter/layout" is unchecked in your case (right click menu -> Table -> External data properties)
Usually it's checked by default. Try to check this property and see repeat inserting column again.
Can't explain that. It shouldn't. Can you share/upload the files that replicate the problem? So an Excel file and the file it connects to, with all the queries as you have them. Otherwise, I can not do anything for you. Perhaps someone else.
I can't believe it, I just did it on the desktop version and I worked fine. It's an issue exclusive to the web version it seems. I prefer the desktop version myself but a lot of my team is the opposite of techy and they prefer the web version so I create most shared docuements in the web version to make sure it works for them.
I could cry over how much time I've wasted and that's all it was...
Unfortunately there’s private info on them so I cannot share. This is just such a project destroying issue that I cannot believe I’m the only one who has ever experienced it.
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.
If one of the documents the query is pulling data from gets an extra column or if a column that was previously removed in a query step but then is changed so it’s no longer removed, both result in all xlookup functions to the right of the new column get misaligned.
How exactly are you "inserting a column in the Power query editor". Tried several ways (splitting a column between the first and the fourth, adding a column and then arranging it in between). No matter what I do, the XLOOKUP formula keeps the reference to the return_array [Column4].
