Create a column with hierarchy information

[Powerwoman]

Hi there,

I have a table:

rowNo	description	totaling rowNo	value
1009	Level 0	1030
1009	Level 0	4820
1030	Level 1	1050
1030	Level 1	1060
1050	data 1		10
1060	data 2		20
4820	data 3		50

I need a column that gives me the level of a row.

Example:
rowNo 1009 totals all rows with rowNo 1030 and 4820. rowNo 1009 is the highest level as it's not included in totalling rowNo

Please note: 4820 should be Level 1, not 2.

Is there a possibility to do this dynamically? Would love to do it in Power Query, not in Dax

Thank you for your help!

[dufoq3 · Accepted solution]

Hi @Powerwoman, another approach:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwsFTSUfJJLUvNUTAAsgwNjEGUUqwOFkkTCyMkSbBCiKQhWKcpPkkzJEmwwpTEkkSwHFgaKmMGlzGCyBhBZKA2g2WMITJAQ2JjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [rowNo = _t, description = _t, #"totaling rowNo" = _t, value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"rowNo", type text}, {"totaling rowNo", type text}, {"value", Int64.Type}}),
    R = Function.Invoke(Record.FromList, Table.ToColumns(Table.SelectRows(ChangedType, each not List.Contains({null, ""}, [totaling rowNo]))[[rowNo], [totaling rowNo]])),
    F = (child, optional lvl)=>
        let l = lvl ?? 0,
            a = Record.FieldOrDefault(R, child, 0), // 0 = highest parent (Level 0)
            b = if a = 0 then l else @F(a, l+1)
        in  b,
    Ad_Level = Table.AddColumn(ChangedType, "Level", each F([rowNo]), type text)
in
    Ad_Level

[v-saisrao-msft]

Hi @Powerwoman 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[wdx223_Daniel]

if the high levels always appear before the low level

[v-saisrao-msft]

Hi @Powerwoman,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[rohit1991]

To dynamically create a hierarchy level column in Power Query, follow these concise steps:

1. Add Level 0 Column:

   • Create a custom column: if [totaling rowNo] = null then 0 else null
     
     

2. 1. Propagate Levels:

      • Use a self-join to match rowNo with totaling rowNo.
      • Increment levels dynamically by adding 1 for each match:
        • Go to Merge Queries and match rowNo to totaling rowNo.
        • Expand the table and increment levels for child rows.

   2. Fill Down Levels:

      • Fill down the levels using Transform → Fill → Down.

   3. Replace Nulls in Levels:

      • Replace remaining null values in the new column with the parent level + 1.

   This creates a dynamic hierarchy column with levels. Let me know if you'd like detailed steps or the M code!

[Powerwoman]

Dear @rohit1991 , thank you for your response. Would be great if you could send me the M Code!
Thank you so much!

[rohit1991]

let
    // Step 1: Load the table (replace 'TableName' with your actual table name)
    Source = TableName,

    // Step 2: Add an initial Level column
    AddLevelColumn = Table.AddColumn(Source, "Level", each if [totaling rowNo] = null then 0 else null, Int64.Type),

    // Step 3: Perform recursive logic using self-joins to propagate levels
    RecursiveMerge = 
        let
            MaxIterations = 10, // Safety limit for recursion
            RecursiveFunction = (CurrentTable, Iteration) =>
                if Iteration > MaxIterations then
                    CurrentTable
                else
                    let
                        // Merge rowNo with totaling rowNo to find parent-child relationships
                        MergedTable = Table.NestedJoin(
                            CurrentTable,
                            {"rowNo"},
                            CurrentTable,
                            {"totaling rowNo"},
                            "Parent",
                            JoinKind.LeftOuter
                        ),
                        // Expand Parent Level and increment levels for child rows
                        ExpandedTable = Table.ExpandTableColumn(
                            MergedTable,
                            "Parent",
                            {"rowNo", "Level"},
                            {"ParentRowNo", "ParentLevel"}
                        ),
                        // Update levels where they are null
                        UpdatedTable = Table.TransformColumns(
                            ExpandedTable,
                            {"ParentLevel", each if _ = null then null else _ + 1, Int64.Type}
                        ),
                        UpdatedLevel = Table.TransformColumns(
                            UpdatedTable,
                            {"Level", each if _ = null then [ParentLevel] else _, Int64.Type}
                        ),
                        CleanedTable = Table.RemoveColumns(UpdatedLevel, {"ParentRowNo", "ParentLevel"})
                    in
                        RecursiveFunction(CleanedTable, Iteration + 1)
        in
            RecursiveFunction(AddLevelColumn, 1),

    // Step 4: Clean up unnecessary columns (optional)
    FinalTable = Table.SelectColumns(RecursiveMerge, {"rowNo", "description", "totaling rowNo", "value", "Level"})
in
    FinalTable

Hi @Powerwoman  , here is a reference too  https://learn.microsoft.com/en-us/powerquery-m/

[Powerwoman]

Dear @rohit1991 :
Thanks for the code. I'm getting this error message:
Expression.Error: The name 'RecursiveFunction' was not recognized. Is it spelled correctly

[PwerQueryKees]

Add @ in front. The offending line would have to be:

@RecursiveFunction(CleanedTable, Iteration + 1)

[Powerwoman]

Dear @AlienSx ,
you are right! 4820 should be Level 1 as there's only one parent-child relationship!

[v-saisrao-msft]

Hi @Powerwoman 
Thanks for reaching for Microsoft fabric Community 

Based on your query regarding creating a column with hierarchy information, please try the following M-query: 

 
Note: Replace the source path with your original source path 

 
CODE: 

let 

    Source = Excel.Workbook(File.Contents("C:\Users\v-saisrao\Downloads\Hierarchy.xlsx"), null, true), 

    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], 

    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), 

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"rowNo", Int64.Type}, {"description", type text}, {"totaling rowNo", type nullable Int64.Type}, {"value", type nullable Int64.Type}}), 

    IsReferenced = (currentRowNo as number) => 

        let 

            ReferencingRows = Table.SelectRows(#"Changed Type", each [totaling rowNo] = currentRowNo) 

        in 

            Table.RowCount(ReferencingRows) > 0, 

    IsReferencedByLevelZero = (currentRowNo as number) => 

        let 

            ReferencingRows = Table.SelectRows(#"Changed Type", each [totaling rowNo] = currentRowNo), 

            Level0Rows = Table.SelectRows(ReferencingRows, each not IsReferenced([rowNo]) and [totaling rowNo] <> null) 

        in 

            Table.RowCount(Level0Rows) > 0, 

    #"Added Level" = Table.AddColumn( 

        #"Changed Type",  

        "Level",  

        each  

            if not IsReferenced([rowNo]) and [totaling rowNo] <> null then 0 

            else if IsReferencedByLevelZero([rowNo]) then 1 

            else if [totaling rowNo] = null and [value] = null then 1 

            else 2, 

        Int64.Type) 

in 

    #"Added Level" 

If this helps, then please Accept it as a solution and dropping a "Kudos" so other members can find it more easily. 

Hope this works for you! 

Thanks. 

[AlienSx]

@Powerwoman a little bit of recursion. If data volume is huge then this may stuck (worst case).

let
    // recursive function to generate level for each rowNo 
    fx_assign_level = (current, level_no, state) => 
        [update_state = Record.FromList(List.Repeat({level_no}, List.Count(current)), current), 
        next_level = List.RemoveNulls(List.Combine(List.Transform(current, (x) => Record.FieldOrDefault(hier, x, {null})))),
        next_state = state & update_state,
        result = if List.IsEmpty(next_level) 
            then next_state 
            else @fx_assign_level(next_level, level_no + 1, next_state)][result],
    // your initial table
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // prepare columns to create a record
    rows_to_text = Table.TransformColumns(Source[[rowNo], [totaling rowNo]], {}, Text.From),
    hier_group = Table.Group(
        Table.SelectRows(rows_to_text, (r) => r[totaling rowNo] <> null), 
        "rowNo",
        {"next", (x) => x[totaling rowNo]}
    ),
    // links between each rowNo and other row numbers in the form of record
    hier = Record.FromList(hier_group[next], hier_group[rowNo]),
    // starting list of row numbers of level zero
    initial_level = List.Distinct(Function.Invoke(List.RemoveItems, Table.ToColumns(rows_to_text))),
    // run recursion 
    invoke_fx = fx_assign_level(initial_level, 0, []), 
    // add column with levels
    add_levels = Table.AddColumn(Source, "Level", (x) => Record.Field(invoke_fx, Text.From(x[rowNo])))
in
    add_levels

[AlienSx]

Hello, @Powerwoman why did you assign Level 2 to 4820 while it's on the same level with 1030?