Excel: Power Query: Self Referencing Query Issue

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.

 

Question Info


Last updated December 2, 2019 Views 890 Applies to:
Answer
Answer

Delete the Comments2 column and refresh the query. That worked for me. Also, starting from scratch with the instructions in the linked article, I never got a Comments2 column. 


___________________
cheers, teylyn
 
Community Moderator
www.teylyn.com

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.