Fixing Path Function | Appending Parent IDs to List of Child Parent Relationships

[tjgagner]

I am working on constructing an relationship in PowerBI that shows the relationships uses the path function documented here: https://www.daxpatterns.com/parent-child-hierarchies/ . One issue I am running into is that the query I have only has the parent/child table imported. This missing component there is that every parent needs to be seperately listed as a child. So what I believe I need to do is take a de-duped list from the ParentID columns and create new rows with the Parent listed as a child. I am looking for help doing this on the PowerQuery side in "M". 

Thanks for taking the time to read and potentially help solve.

From		To
ParentID	ChildID		ParentID	ChildID
16	1		16	1
16	2		16	2
16	3		16	3
16	4		16	4
16	5		16	5
17	6		17	6
17	7		17	7
17	8		17	8
17	9		17	9
17	10		17	10
18	11		18	11
18	12		18	12
18	13		18	13
18	14		18	14
18	15		18	15
			null	16
			null	17
			null	18

[vicky_ · Accepted solution]

In power query, duplicate the orignial table, and re-work it so that you have the unique parent IDs as child IDs. Then you can append the second table back into the original table to get your desired result. My steps to transform the second table are listed below:

let
    Source = //your source here,
    #"Removed Columns" = Table.RemoveColumns(#Source,{"ChildID"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"ParentID", "ChildID"}}),
    Custom1 = Table.AddColumn(#"Renamed Columns", "ParentID", each null)
in
    Custom1

 Hope this helps!

[tjgagner]

This worked for me, thanks @vicky_ !