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

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!

5 Replies

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

  • jgeddes's avatar
    jgeddes
    Super User
    3 years ago

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

    • joshua1990's avatar
      joshua1990
      Post Prodigy
      3 years ago

      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's avatar
        jgeddes
        Super User
        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!