Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Creating a hierarchy from indented column

I have the following table:    It has a column that contains levels of indentation signifying a hierarchical relationship. I'd like to ask how this could be unwrapped into the following table...
Need Help
Show and Tell
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    Try this.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgQCpVidaCUFJyCAsBScDUEAynE0AgEYxxgEIBxnICBaQywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Block = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Level", each "Block Level " & Number.ToText( List.Count( List.Accumulate( Text.ToList( [Block] ), {" "}, ( s,a ) => if a = List.Last(s) then s & {a} else s ) ) - 1 ) ),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Block", Text.Trim, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Trimmed Text", List.Distinct(#"Trimmed Text"[Level]), "Level", "Block"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Block Level 0", "Block Level 1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Block Level 2] <> null))
in
    #"Filtered Rows"

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Anonymous 

 

Try this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgQCpVidaCUFJyCAsBScDUEAynE0AgEYxxgEIBxnICBaQywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Block = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Level", each "Block Level " & Number.ToText( List.Count( List.Accumulate( Text.ToList( [Block] ), {" "}, ( s,a ) => if a = List.Last(s) then s & {a} else s ) ) - 1 ) ),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Block", Text.Trim, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Trimmed Text", List.Distinct(#"Trimmed Text"[Level]), "Level", "Block"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Block Level 0", "Block Level 1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Block Level 2] <> null))
in
    #"Filtered Rows"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you very much for your help so far Mariusz! Your solution is very close however I can't seem to sort out the final format which currently is this:

I think it may be due to different indentation levels between your source data and mine? 

  • Miralon's avatar
    Miralon
    Frequent Visitor
    6 years ago

    Mariusz's solution helped me to start digging into M functions. 

    I think I'll replace #"Added Custom" step with the next one, as you have to calculate level by number of leading spaces.

    Is just my first try into M functions, hope this will help. 

    #"Added Custom" = Table.AddColumn(#"Added Index", "Level"
    , each "Block Level " & Number.ToText(Text.Length([Block])-Text.Length(Text.TrimStart([Block])))),