Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
3 years ago
Solved

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...
Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    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's avatar
jgeddes
Super User
3 years ago

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's avatar
    joshua1990
    Post Prodigy
    3 years ago

    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.