Create a column with hierarchy information

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

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. 

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