Multi Level Parent Child Hierarchy

[joshua1990]

Hi all!

I have a flat file that shows me the relation for each code combination:

Parent	Child
X	X1
X1	X12
X1	X13
A	A1
A1	A2

 As you can see we have a multi level relation here between both column.

Now I would like to get a result this construct:

Parent	Child	Level 1	Level 2	Level 3	Level 4
X	X1	TRUE	FALSE	FALSE	FALSE
X1	X12	FALSE	TRUE	FALSE	FALSE
X1	X13	FALSE	TRUE	FALSE	FALSE

How is that possible using Power Query?

I don't know how many levels we have in the end.

[jgeddes]

What would dictate if a value is Level 1 or Level 2 etc?

[joshua1990]

This is not dictatedin the data right now. This is the exercise / request here. Calculate the number of Levels for each hierarchy / relation between the codes and display the individual level for each combination.

It is always Parent To Child. Of there is no relation more downstream, then it is the end. The same applies for Level 1. If nothing is above, then it is Level 1

[jgeddes · Accepted solution]

Alright then. Let's try this...

For the dataset

add the [Child] column as a new query and remove duplicates

now test the [Parent] column to see if the value is a "master" parent

Table.AddColumn(#"Changed Type1", "masterParent", each if List.Contains(Child, [Parent]) then null else [Parent])

Fill down the [masterParent] column

Table.FillDown(#"Added Custom",{"masterParent"})

Group By [masterParent] and [Parent] with now aggregation of rows

Table.Group(#"Filled Down", {"Parent", "masterParent"}, {{"Group1", each _, type table [Parent=nullable text, Child=nullable text, master=text]}})

Now Group By [masterParent] with no aggregation

Table.Group(#"Grouped Rows", {"masterParent"}, {{"Group2", each _, type table [Parent=nullable text, master=text, Count=table]}})

Add an index column

Table.AddColumn(#"Grouped Rows1", "Index", each Table.AddIndexColumn([Group2], "Index", 1))

Remove the [masterParent] and [Group2] columns

Table.RemoveColumns(#"Added Custom1",{"masterParent", "Group2"})

expand the [Index] column

Table.ExpandTableColumn(#"Removed Columns", "Index", {"Parent", "masterParent", "Group1", "Index"}, {"Parent", "masterParent", "Group1", "Index"})

remove the [Parent] and [masterParent] columns

Table.RemoveColumns(#"Expanded Custom",{"Parent", "masterParent"})

expand [Group1] column

Table.ExpandTableColumn(#"Removed Columns1", "Group1", {"Parent", "Child", "masterParent"}, {"Parent", "Child", "masterParent"})

add a Prefix to the [Index] column

Table.TransformColumns(#"Expanded Count", {{"Index", each "Level " & Text.From(_, "en-US"), type text}})

add a boolean true column

Table.AddColumn(#"Added Prefix", "_boolean", each true)

add the [Index] column as a new query and remove duplicates

pivot the [Index] column using the [_boolean] column as values with no row aggregation

Table.Pivot(#"Added Custom2", List.Distinct(#"Added Custom2"[Index]), "Index", "_boolean")

replace the null values with "FALSE", using the Index list to ensure it will work dynamically

Table.ReplaceValue(#"Pivoted Column",null,false,Replacer.ReplaceValue,Index)

and finally remove the [masterParent] column

Table.RemoveColumns(#"Replaced Value",{"masterParent"})

Hope this helps!

[jgeddes]

As with all things Power Query there are likely other (better) ways of doing this, but this should get you going in the right direction.

I created an excel file with your data as the source

added a column to populate "True" values 
Table.AddColumn(#"Changed Type1", "childBoolean", each if [Child] <> null then true else false)

added a column to get the length of the [Child] values (subtracted 1 to make sure column names are correct)

Table.AddColumn(#"Added Custom", "childLength", each Text.Length([Child]) - 1)

add a prefix to the [childLength] column to name the resulting columns correctly

added the [childLength] column as a new query, converted it to a list and removed duplicates to get

back in the main query, I pivoted the childLength column using the childBoolean column as values, not aggregating

Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[childLength]), "childLength", "childBoolean")

the null values are replaced using the childLength list created earlier to ensure the columns are dynamically updated

Table.ReplaceValue(#"Pivoted Column",null,false,Replacer.ReplaceValue,childLength)

Hope this helps.

[joshua1990]

@jgeddes : Thanks a lot! Sorry for not making this clear. But the length of the string has nothing to do with the level. It was just used to demonstrate the relation more clearly.