Power Query - Self Referencing Tables for adding comments etc

[davidmaddock54]

So after reading this thread I figured I'd try my hand and follow instructions from this link.

However, after a bit of testing this morning, I can't quite seem to get it to work. I can get a comments section in, and it doesn't disappear after a refresh, but it's not logically linked to the data.

It does appear that this is an older workaround, with edits in 2017 and 2020 to account for changes in PQ. 

I have attached my attempted spreadsheet below.

Any assistance  gladly received!

[#2 · Accepted solution]

@davidmaddock54 The posting you referred to included one link (of many) to an article about how to create self referencing tables in PQ. Looking at your particular situation, perhaps the attached version works better for you. All is condensed into one query.

See if you can follow the steps, though it required some manipulation of the M-code. The key is that you create two Sources and merge one to the other within the same query.

[#3]

Once again, life saver! Took me a second to work out how to add the second custom step to create what you called "Source2" but once I got that, the rest makes sense.

Now I need to think about the ramifications of the data being editable by folks using it!

[#4]

Hello. I have been trying to create a Self Referencing Table with 4 additional collumns from my query / pivot table. I have gotten to the very end, however every time I import my merged queries, it is showing duplicate columns.

@Riny_van_Eekelen 

[#5]

@mollynoel98 

Just remove duplicated columns in the resulting table and refresh. The won't appear again. If you merge correctly.

[#6]

The problem is the table that it is duplicating is linked to my query as the "template" for how I want my page to look. If I remove the columns, then my query breaks. @SergeiBaklan 

[#7]

@mollynoel98 

Perhaps we misunderstood each other. How self-referencing works:

- we have some query which returns table into the grid. Such query/table shall have column with unique values (Id)

- we add to that returned table some columns and query such expanded table

- within initial query (not as separate query) we add merging of latest step of initial query with query which takes the table with additional columns

- we may use helper query to perform above merging, copy/paste steps into initial query and remove helper query. Thus we have only one query for that.

- after returning modified initial query into the grid it could appear duplicated columns which were added manually, we only need to remove them and refresh again. They won't appear if additional columns are added after the merging in the query.

That could be other modifications, general idea is we work with one table and one query.

[#8]

So I rewrote it to where the collateral pipeline is my master query. I then created a 2nd table from that query labeled "source" and added in my columns. However, when I go back to Power Query Editor, it is not showing my added columns so I am unable to then merge.

[#9]

@SergeiBaklan

[#10]

@mollynoel98 

let me do step by step.

We have source Table1 with keys column Id, query it and return back as it is

Returned table has default name Table1_1. Add another column to it

and query this modified Table1_1.

Merge first query with it on Id and expand only Comment column.

Next copy/paste second query source into first one, rename it as Table1_1, and delete second query

Entire code of the first query now is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Table1_1 = Excel.CurrentWorkbook(){[Name="Table1_1"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source, {"Id"}, Table1_1, {"Id"}, "Table1_1", JoinKind.LeftOuter),
    #"Expanded Table1_1" = Table.ExpandTableColumn(#"Merged Queries", "Table1_1", {"Comment"}, {"Comment"})
in
    #"Expanded Table1_1"

We have only one query now

which returns duplicated manually added column(s)

Let remove here duplicated column, refresh query and sort the table to check if new column is in sync

It works

You may check above in attached file.