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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Apologies Mariusz , perhaps my example problem was too generic. I have updated my question to reflect the true scenario which i have where I believe your specific solution might not work.

  • 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])))),