Home » Pivot Hierarchical Sublevels

Pivot Hierarchical Sublevels

Pivot the data as shown. Data for level 1 will appear against 1, 2, 3 (level column) and 0, 1, 2, 3, 4 column values are for sublevels 0, 1, 2, 3, 4 (1, 1.1, 1.2 and so on). Try to be dynamic so if levels / sublevels increase or decrease.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 663
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Pivot Hierarchical Sublevels with Power Query

Power Query solution 1 for Pivot Hierarchical Sublevels, proposed by Kris Jaganah: 
let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = Table.SplitColumn(
    A, 
    "Level", 
    each [a = Text.From(_), b = if Text.Contains(a, ".") then Text.Split(a, ".") else {a, "0"}][b], 
    {"Level", "Spl"}
  ), 
  C = Table.Pivot(B, List.Distinct(B[Spl]), "Spl", "Value", each _{0}?)
in
  C
Power Query solution 2 for Pivot Hierarchical Sublevels, proposed by Vida Vaitkunaite: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Split = Table.SplitColumn(
    Source, 
    "Level", 
    each {
      Text.BeforeDelimiter(Text.From(_), "."), 
      if Text.Contains(Text.From(_), ".") then Text.AfterDelimiter(Text.From(_), ".") else "0"
    }, 
    {"Level", "Level2"}
  ), 
  Final = Table.Pivot(Split, List.Distinct(Split[Level2]), "Level2", "Value")
in
  Final
Power Query solution 3 for Pivot Hierarchical Sublevels, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Split = Table.TransformColumns(
    Source, 
    {
      {
        "Level", 
        each 
          let
            a = _, 
            b = Text.From(a), 
            c = if Text.Contains(b, ".") then b else b & ".0", 
            d = Table.FromRows({Text.Split(c, ".")}, {"Level", "B"})
          in
            d
      }
    }
  ), 
  Expand = Table.ExpandTableColumn(Split, "Level", {"Level", "B"}), 
  Sol = Table.Pivot(Expand, List.Distinct(Expand[B]), "B", "Value")
in
  Sol
Power Query solution 4 for Pivot Hierarchical Sublevels, proposed by Abdallah Ally: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    "Level", 
    {
      "Data", 
      each Table.FromRows({[Value]}, List.Transform({0 .. List.Count([Value]) - 1}, Text.From))
    }, 
    0, 
    (x, y) => Value.Compare(Number.RoundDown(x), Number.RoundDown(y))
  ), 
  Result = Table.ExpandTableColumn(Group, "Data", Table.ColumnNames(Table.Combine(Group[Data])))
in
  Result
Power Query solution 5 for Pivot Hierarchical Sublevels, proposed by Ramiro Ayala Chávez: 
let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a = Table.TransformColumnTypes(S, {"Level", type text}), 
  b = Table.TransformColumns(a, {"Level", each if Text.Length(_) = 1 then _ & ",0" else _}), 
  c = Table.SplitColumn(b, "Level", Splitter.SplitTextByDelimiter(","), {"L1", "L2"}), 
  d = Table.DemoteHeaders(
    Table.Pivot(Table.Sort(c, {"L2", 0}), List.Distinct(c[L2]), "L2", "Value")
  ), 
  Sol = Table.ReplaceValue(d, "L1", Table.ColumnNames(b){0}, Replacer.ReplaceText, {"Column1"})
in
  Sol
Power Query solution 6 for Pivot Hierarchical Sublevels, proposed by Alexandre Garcia: 
let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = Table.FromRows(
    Table.ToList(
      A, 
      each 
        let
          x = Text.ToList(Text.From(_{0}))
        in
          {x{0}, x{2}? ?? "0", _{1}}
    ), 
    {"x", "y", "z"}
  ), 
  C = Table.Combine(
    Table.Group(
      B, 
      "x", 
      {"y", each Table.FromRecords({[Level = [x]{0}] & Record.FromList([z], [y])})}
    )[y]
  )
in
  C
Power Query solution 7 for Pivot Hierarchical Sublevels, proposed by Krzysztof Kominiak: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  A = Table.AddColumn(Source, "tmp", each Number.Round(Number.Mod([Level], 1) * 10)), 
  B = Table.TransformColumns(A, {"Level", each Number.Round(_, 0)}), 
  Result = Table.Pivot(
    Table.TransformColumnTypes(B, {{"tmp", type text}}), 
    List.Distinct(Table.TransformColumnTypes(B, {{"tmp", type text}})[tmp]), 
    "tmp", 
    "Value"
  )
in
  Result
Power Query solution 8 for Pivot Hierarchical Sublevels, proposed by Sahan Jayasuriya: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content], 
  SplitCol = Table.SplitColumn(
    Table.TransformColumnTypes(Source, {{"Level", type text}}, "en-US"), 
    "Level", 
    each {
      Text.BeforeDelimiter(_, "."), 
      if Text.AfterDelimiter(_, ".") = "" then "0" else Text.AfterDelimiter(_, ".")
    }, 
    {"Level", "Col"}
  ), 
  Pivot = Table.Pivot(SplitCol, List.Distinct(SplitCol[Col]), "Col", "Value", List.Sum)
in
  Pivot

Solving the challenge of Pivot Hierarchical Sublevels with Excel

Excel solution 1 for Pivot Hierarchical Sublevels, proposed by Bo Rydobon 🇹🇭: 
=LET(l,A2:A10,p,PIVOTBY(INT(l),MOD(l*10,10),B2:B10,SINGLE,,0,,0),IF(TAKE(p,1)&TAKE(p,,1)="","Level",p))
Excel solution 2 for Pivot Hierarchical Sublevels, proposed by Rick Rothstein: 
=IFNA(DROP(REDUCE(E2:I2,D3:D5,LAMBDA(a,x,VSTACK(a,TRANSPOSE(FILTER(B2:B10,INT(A2:A10)=x))))),1),"")

Assuming the header are not there...
=LET(v,UNIQUE(INT(A2:A10)),h,HSTACK(v,IFNA(DROP(REDUCE({0,1,2,3,4},v,LAMBDA(a,x,VSTACK(a,TRANSPOSE(FILTER(B2:B10,INT(A2:A10)=x))))),1),"")),VSTACK(HSTACK("Level",SEQUENCE(,COLUMNS(h)-1,0)),h))
Excel solution 3 for Pivot Hierarchical Sublevels, proposed by John V.: 
=LET(i,A2:A10,p,PIVOTBY(INT(i),MOD(10*i,10),B2:B10,SUM,,0,,0),IF((p<"")+SEQUENCE(ROWS(p))-1,p,"Level"))
Excel solution 4 for Pivot Hierarchical Sublevels, proposed by Kris Jaganah: 
=LET(a,
    A2:A10,
    b,
    INT(
        a
    ),
    c,
    INT((a-b)*10),
    d,
    PIVOTBY(
        b,
        c,
        B2:B10,
        MIN,
        ,
        0,
        ,
        0
    ),
    IF(
        SCAN(
            ,
            d,
            CONCAT
        )="",
        "Level",
        d
    ))
Excel solution 5 for Pivot Hierarchical Sublevels, proposed by Timothée BLIOT: 
=PIVOTBY(
    LEFT(
        A2:A10
    ),
    RIGHT(
        TEXT(
            A2:A10,
            "0.0"
        )
    ),
    B2:B10,
    SUM,
    ,
    0,
    ,
    0
)
Excel solution 6 for Pivot Hierarchical Sublevels, proposed by Duy Tùng: 
=LET(a,PIVOTBY(INT(A2:A10),MOD(A2:A10*10,10),B2:B10,SUM,,0,,0),IF(SEQUENCE(ROWS(a),COLUMNS(a))=1,A1,a))

=LET(a,INT(A2:A10),REDUCE(HSTACK(A1,SEQUENCE(,MAX(FREQUENCY(a,a)),0)),UNIQUE(a),LAMBDA(c,v,IFNA(VSTACK(c,HSTACK(v,TOROW(FILTER(B2:B10,a=v)))),""))))
Excel solution 7 for Pivot Hierarchical Sublevels, proposed by Sunny Baggu: 
=LET(
 _a, FIXED(A2:A10, 1),
 _b, UNIQUE(LEFT(_a)) + 0,
 _c, TOROW(UNIQUE(RIGHT(_a))),
 _d, (_b & "." & _c) + 0,
 _e, XLOOKUP(_d, A2:A10, B2:B10, ""),
 VSTACK(HSTACK(A1, _c), HSTACK(_b, _e))
)
Excel solution 8 for Pivot Hierarchical Sublevels, proposed by Anshu Bantra: 
=LET(
data_,A2:B10,
rows_,--IFNA(TEXTBEFORE(CHOOSECOLS(data_,1),"."),CHOOSECOLS(data_,1)),
cols_,--IFNA(TEXTAFTER(CHOOSECOLS(data_,1),"."),0),
table_,PIVOTBY(rows_,cols_,CHOOSECOLS(data_,2),SUM,,0,,0),
matrix_, MAKEARRAY(COUNT(UNIQUE(rows_))+1,COUNT(UNIQUE(cols_))+1,LAMBDA(r,c,((r-1)+(c-1)))),
IF(matrix_=0,"Level",table_)
)
Excel solution 9 for Pivot Hierarchical Sublevels, proposed by Md. Zohurul Islam: 
=LET(
    
    u,
    A2:A10,
    
    v,
    B2:B10,
    
    a,
    UNIQUE(
        INT(
            u
        )
    ),
    
    b,
    SEQUENCE(
        ,
        MAX(
            a
        )+2,
        0
    ),
    
    d,
    MAP(
        ABS(
            a&"."&b
        ),
        LAMBDA(
            x,
            XLOOKUP(
                x,
                u,
                v,
                ""
            )
        )
    ),
    
    e,
    HSTACK(
        VSTACK(
            "Level",
            a
        ),
        VSTACK(
            b,
            d
        )
    ),
    
    e
)
Excel solution 10 for Pivot Hierarchical Sublevels, proposed by Pieter de B.: 
=LET(
    p,
    PIVOTBY(
        LEFT(
            A1:A10
        ),
        TEXTAFTER(
            A1:A10,
            ".",
            ,
            ,
            ,
            0
        ),
        B1:B10,
        MIN,
        ,
        0,
        ,
        0
    ),
    IF(
        SEQUENCE(
            ROWS(
                p
            )
        )-1,
        p,
        IF(
            p="",
            "Level",
            p
        )
    )
)
Excel solution 11 for Pivot Hierarchical Sublevels, proposed by Hamidi Hamid: 
=LET(x,INT(A2:A10),g,GROUPBY(x,B2:B10,ARRAYTOTEXT,,0),f,DROP(g,,1),s,HSTACK(TAKE(g,,1),IFERROR(DROP(REDUCE(0,f,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,", ",,)))),1),"")),VSTACK(HSTACK("Level",DROP(SEQUENCE(,COLUMNS(s))-1,,-1)),s))
Excel solution 12 for Pivot Hierarchical Sublevels, proposed by Asheesh Pahwa: 
=LET(
    d,
    A2:A10,
    t,
    TEXT(
        d,
        "0.0"
    ),
    l,
    UNIQUE(
        LEFT(
            d
        )
    ),
    r,
    TOROW(
        UNIQUE(
            RIGHT(
            d
        )
        )
    ),
    h,
    HSTACK(
        0,
        r
    ),
    VSTACK(
        HSTACK(
            "Level",
            h
        ),
        HSTACK(
            l,
            XLOOKUP(
                l&"."&h,
                t,
                B2:B10,
                ""
            )
        )
    )
)
Excel solution 13 for Pivot Hierarchical Sublevels, proposed by ferhat CK: 
=LET(a,PIVOTBY(IFNA(TEXTBEFORE(A2:A10,","),A2:A10),IFNA(TEXTAFTER(A2:A10,","),0),B2:B10,MAX,,0,,0),IF(SCAN(,a,COUNT)="","Level",a))
Excel solution 14 for Pivot Hierarchical Sublevels, proposed by Jaroslaw Kujawa: 
=MAKEARRAY(4;6;LAMBDA(r;c;IF((r=1)*(c=1);"Level";IF(r=1;c-2;IF(c=1;r-1;IFNA(XLOOKUP(1*(r-1&"."&c-2);A3:A11;B3:B11);""))))))
Excel solution 15 for Pivot Hierarchical Sublevels, proposed by Jaroslaw Kujawa: 
=LET(x;A3:A11;a;SEQUENCE(;1+MAX(1*TAKE(GROUPBY(RIGHT(x);x;MAX;;0);;1));0);b;SEQUENCE(MAX(1*TAKE(GROUPBY(LEFT(x);RIGHT(x);MAX;;0);;1)););c;MAKEARRAY(MAX(b);MAX(a)+1;LAMBDA(r;c;IFNA(XLOOKUP(1*(r&"."&c-1);x;OFFSET(x;;1));"")));HSTACK(VSTACK("Level";b);VSTACK(a;c)))
Excel solution 16 for Pivot Hierarchical Sublevels, proposed by Ankur Sharma: 
=LET(
    r,
     A2:A10,
     at,
     ARRAYTOTEXT,
     tb,
     TEXTBEFORE,
    
    a,
     MAX(
         --TEXTAFTER(
             r,
              ".",
              ,
              ,
              ,
              0
         )
     ) + 1,
    
    b,
     at(
         SEQUENCE(
             1,
              a,
              0
         )
     ),
    
    c,
     tb(
         r,
          ".",
          ,
          ,
          ,
          r
     ),
    
    d,
     DROP(
         GROUPBY(
             tb(
                 r,
                  ".",
                  ,
                  ,
                  ,
                  r
             ),
              B2:B10,
              at,
              ,
              0
         ),
          ,
          1
     ),
    
    HSTACK(
        VSTACK(
            "Level",
             UNIQUE(
                 c
             )
        ),
         TEXTSPLIT(
             TEXTJOIN(
                 "-",
                  ,
                  b,
                  d
             ),
              ", ",
              "-",
              ,
              ,
              ""
         )
    )
)
Excel solution 17 for Pivot Hierarchical Sublevels, proposed by Meganathan Elumalai: 
=LET(a,PIVOTBY(INT(A2:A10),TEXTAFTER(A2:A10,".",,,,0),B2:B10,SUM,,0,,0),IF(TAKE(a,1)&TAKE(a,,1)="","Level",a))
Excel solution 18 for Pivot Hierarchical Sublevels, proposed by Peter Bartholomew: 
= LET(
 textID, TEXT(level, "#0.0"),
 level1, TEXTBEFORE(textID, "."),
 level2, TEXTAFTER(textID, "."),
 PIVOTBY(level1, level2, value, SUM,,0,,0)
 )

&&&

Leave a Reply