Forum Discussion
Power Query - Self Referencing Tables for adding comments etc
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.
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
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.
- 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.
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.
