cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joshua1990
Super User
Super User

Multi Level Parent Child Hierarchy

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.

1 ACCEPTED SOLUTION

Alright then. Let's try this...

For the dataset

jgeddes_0-1669996881109.png

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

jgeddes_1-1669996957108.png

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])

jgeddes_2-1669997017119.png

Fill down the [masterParent] column

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

jgeddes_3-1669997082533.png

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]}})

jgeddes_4-1669997143327.png

Now Group By [masterParent] with no aggregation

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

jgeddes_5-1669997173357.png

Add an index column

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

jgeddes_6-1669997237565.png

 

Remove the [masterParent] and [Group2] columns

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

jgeddes_7-1669997260501.png

expand the [Index] column

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

jgeddes_8-1669997285481.png

remove the [Parent] and [masterParent] columns

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

jgeddes_9-1669997326120.png

expand [Group1] column

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

jgeddes_10-1669997353381.png

add a Prefix to the [Index] column

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

jgeddes_11-1669997473623.png

add a boolean true column

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

jgeddes_12-1669997526016.png

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

jgeddes_13-1669997564263.png

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")

jgeddes_14-1669997630588.png

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)

jgeddes_15-1669997703174.png

and finally remove the [masterParent] column

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

jgeddes_16-1669997735336.png

 

Hope this helps!

View solution in original post

5 REPLIES 5
jgeddes
Solution Sage
Solution Sage

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

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

Alright then. Let's try this...

For the dataset

jgeddes_0-1669996881109.png

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

jgeddes_1-1669996957108.png

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])

jgeddes_2-1669997017119.png

Fill down the [masterParent] column

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

jgeddes_3-1669997082533.png

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]}})

jgeddes_4-1669997143327.png

Now Group By [masterParent] with no aggregation

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

jgeddes_5-1669997173357.png

Add an index column

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

jgeddes_6-1669997237565.png

 

Remove the [masterParent] and [Group2] columns

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

jgeddes_7-1669997260501.png

expand the [Index] column

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

jgeddes_8-1669997285481.png

remove the [Parent] and [masterParent] columns

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

jgeddes_9-1669997326120.png

expand [Group1] column

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

jgeddes_10-1669997353381.png

add a Prefix to the [Index] column

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

jgeddes_11-1669997473623.png

add a boolean true column

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

jgeddes_12-1669997526016.png

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

jgeddes_13-1669997564263.png

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")

jgeddes_14-1669997630588.png

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)

jgeddes_15-1669997703174.png

and finally remove the [masterParent] column

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

jgeddes_16-1669997735336.png

 

Hope this helps!

jgeddes
Solution Sage
Solution Sage

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

jgeddes_0-1669911652422.png

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

jgeddes_1-1669911729447.png

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)

jgeddes_2-1669911841727.png

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

jgeddes_3-1669911931711.png

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

jgeddes_4-1669911951463.png

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")

jgeddes_5-1669912039185.png

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)

jgeddes_6-1669914154813.png

 

Hope this helps.

@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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors