Forum Discussion

davidmaddock54's avatar
davidmaddock54
Brass Contributor
Sep 26, 2022
Solved

Power Query - Self Referencing Tables for adding comments etc

So after reading this thread I figured I'd try my hand and follow instructions from this https://exceleratorbi.com.au/self-referencing-tables-power-query/   However, after a bit of testing this mor...
excel
power bi
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Sep 27, 2022

    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.

mollynoel98's avatar
mollynoel98
Copper Contributor
Sep 27, 2023
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.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 27, 2023

    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.

Resources