Forum Discussion

Powerwoman's avatar
Powerwoman
Helper II
1 year ago
Solved

Create a column with hierarchy information

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   ...
Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    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

     

Powerwoman's avatar
Powerwoman
Helper II
1 year ago

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

 

AlienSx's avatar
AlienSx
Super User
1 year ago

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