Hello and thank you for reading my first ever help request.
Platform: Windows 10
Application: Excel Version 1609 (Build 7369.2095)
Problem:
Adding an additional column (CX) to a query output table (T2) and self referencing T2 to maintain record (row) references in tact after sorting and refreshing data connections.
Recently stumbled upon a great article at exceleratorbi dot com dot au. The authors' article details using Power Query to add an new (additional) column to an existing query, self reference the query and Refresh All, sort and filter while keeping the record (row) data relative to the cell in which it was entered.
Link to article: Self Referencing Tables in Power Query
Had been searching a long time for this solution. It does not seem to be a common question.
Workflow
Create an excel table with data (T1)
Create a query based on that table and output to spreadsheet (T2)
Add a column at the end of the query table T2
Create a new query from table T2: Connection only
Merge query for T2 and the T2 connection query
Output the data
Anyway, tried the method on the post and had the following issue:
How the query output to the Excel spreadsheet should be (Post authors' output on post authors' computer):
How the query output to the Excel spreadsheet should be (My output on my PC):
We have compared creation steps of the query and compared code; both are the same. Tried this at least 15 times; rinse and repeat.
If you look at the article (link above) and watch the video (see comments at bottom of post), you will notice in the post author's version that when the query is run, there is a brief column expansion to the right of the table, then it disappears. On my version, the new column does not disappear.
Quick expansion and contraction after running query (Matt's version)
Here is a link to the Excel file with the Power Query queries.
Power Query Self Reference Test 2
Any assistance is appreciated to make this work as intended.
