Transpose Departments into Row

Transpose the data in a single row for all departments

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

Solving the challenge of Transpose Departments into Row with Power Query

Power Query solution 1 for Transpose Departments into Row, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  CN = Table.ColumnNames(Source), 
  Combine = Table.Combine(
    Table.Group(
      Source, 
      "Dept", 
      {
        "T", 
        each Table.FromRows(
          {
            {[Dept]{0}}
              & Text.Split(
                Text.Combine(
                  Table.TransformRows(
                    _, 
                    each Record.Field(_, CN{1}) & ", " & Record.Field(_, CN{2})
                  ), 
                  ", "
                ), 
                ", "
              )
          }, 
          {"Dept"}
            & List.Combine(
              List.Transform(
                {1 .. Table.RowCount(_)}, 
                each 
                  let
                    n = Text.From(_)
                  in
                    Text.Split(Text.Replace(CN{1} & " & " & CN{2} & n, " &", n), " ")
              )
            )
        )
      }
    )[T]
  )
in
  Combine

Power Query solution 2 for Transpose Departments into Row, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  Sorting = List.Distinct(Source[Dept]), 
  GroupedDept = Table.ExpandListColumn(
    Table.ExpandListColumn(
      Table.Group(
        Source, 
        {"Dept"}, 
        {
          {
            "All", 
            each Table.AddColumn(
              Table.AddIndexColumn(_, "ID", 1), 
              "List", 
              each 
                let
                  r = {[Employee]} & Text.Split([#"Age & Nationality & Salary"], ", "), 
                  c = {"Employee", "Age", "Nationality", "Salary"}
                in
                  List.Accumulate(
                    List.Positions(r), 
                    {}, 
                    (s, d) => s & {{[Dept], c{d} & Text.From([ID]), r{d}}}
                  )
            )[List]
          }
        }
      ), 
      "All"
    ), 
    "All"
  ), 
  ExpandedTable = Table.FromRows(GroupedDept[All], {"Dept", "Attribute", "Value"}), 
  PivotedColumn = Table.Pivot(
    ExpandedTable, 
    List.Distinct(ExpandedTable[Attribute]), 
    "Attribute", 
    "Value"
  ), 
  SortedRows = Table.Sort(
    PivotedColumn, 
    (a, b) => Value.Compare(List.PositionOf(Sorting, a[Dept]), List.PositionOf(Sorting, b[Dept]))
  )
in
  SortedRows

Power Query solution 3 for Transpose Departments into Row, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  MyFun = (Table as table) as table =>
    let
      Remove = Table.RemoveColumns(Table, {"Dept"}), 
      WorkCols = Table.ColumnNames(Remove), 
      Fields = List.TransformMany(
        {1 .. Table.RowCount(Remove)}, 
        (y) => WorkCols, 
        (x, y) => y & "" & Text.From(x)
      ), 
      Values = List.Combine(Table.ToRows(Remove)), 
      Records = [Dept = Table.FirstValue(Table)] & Record.FromList(Values, Fields), 
      Final = Table.FromRecords({Records})
    in
      Final, 
  OldName = List.Last(Table.ColumnNames(Source)), 
  NewName = Text.Split(OldName, " & "), 
  Split = Table.SplitColumn(Source, OldName, Splitter.SplitTextByDelimiter(", "), NewName), 
  Group = Table.Group(Split, "Dept", {"All", MyFun}), 
  Return = Table.Combine(Group[All])
in
  Return

Power Query solution 4 for Transpose Departments into Row, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Split = Table.SplitColumn(
    Source, 
    "Age & Nationality & Salary", 
    Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), 
    {"Age", "Nationality", "Salary"}
  ), 
  Group = Table.Group(
    Split, 
    {"Dept"}, 
    {
      {
        "Count", 
        each 
          let
            a = Table.ToRows(_), 
            b = List.Combine(List.Transform(a, List.Skip)), 
            c = Table.FromRows({b})
          in
            c
      }
    }
  ), 
  Col = List.Distinct(
    List.Combine(Table.AddColumn(Group, "Col", each Table.ColumnNames([Count]))[Col])
  ), 
  ColName = List.Skip(Table.ColumnNames(Split)), 
  Rep = List.Count(Col) / List.Count(ColName), 
  Headers = List.Combine(
    List.Transform({1 .. Rep}, (x) => List.Transform(ColName, each _ & Text.From(x)))
  ), 
  Sol = Table.FromColumns(
    {Group[Dept]} & Table.ToColumns(Table.Combine(Group[Count])), 
    {"Dept"} & Headers
  )
in
  Sol

Power Query solution 5 for Transpose Departments into Row, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  gp = Table.Group(
    Fonte, 
    {"Dept"}, 
    {
      {
        "Contagem", 
        each [
          a = Table.AddIndexColumn(_[[Employee], [#"Age & Nationality & Salary"]], "Ind", 1, 1), 
          c = Table.AddColumn(a, "Dados", each [Employee] & ", " & [#"Age & Nationality & Salary"])[
            [Dados], 
            [Ind]
          ], 
          d = Table.AddColumn(
            c, 
            "cab", 
            each Text.Combine(
              List.Transform(
                {"Employee", "Age", "Nationality", "Salary"}, 
                (x) => x & Text.From([Ind])
              ), 
              ", "
            )
          )[[cab], [Dados]]
        ][d]
      }
    }
  ), 
  exp = Table.ExpandTableColumn(gp, "Contagem", {"cab", "Dados"}), 
  div = Table.ExpandListColumn(
    Table.TransformColumns(
      exp, 
      {
        {
          "cab", 
          Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "cab"
  ), 
  gp2 = Table.Group(
    div, 
    {"Dept", "Dados"}, 
    {{"Contagem", each Table.AddIndexColumn(_, "Ind", 0, 1)}}
  )[[Contagem]], 
  exp2 = Table.ExpandTableColumn(gp2, "Contagem", Table.ColumnNames(gp2[Contagem]{0})), 
  split = Table.AddColumn(
    exp2, 
    "Personalizar", 
    each Splitter.SplitTextByAnyDelimiter({", "}, QuoteStyle.Csv)([Dados]){[Ind]}
  )[[Dept], [cab], [Personalizar]], 
  sort = gp[Dept], 
  res = Table.Sort(
    Table.Pivot(split, List.Distinct(split[cab]), "cab", "Personalizar"), 
    each List.PositionOf(sort, [Dept])
  )
in
  res

Power Query solution 6 for Transpose Departments into Row, proposed by Eric Laforce:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table86"]}[Content], 
  Split_ANS = Table.SplitColumn(
    Source, 
    "Age & Nationality & Salary", 
    Splitter.SplitTextByDelimiter(",", QuoteStyle.None), 
    {"Age", "Nationality", "Salary"}
  ), 
  Group = Table.Group(
    Split_ANS, 
    {"Dept"}, 
    {
      {
        "Data", 
        each 
          let
            _List = List.Accumulate(
              Table.ToRows(Table.RemoveColumns(_, "Dept")), 
              {}, 
              (s, a) => s & a
            ), 
            _Combine = Text.Combine(List.Transform(_List, Text.From), ";")
          in
            _Combine
      }, 
      {"RowCount", each Table.RowCount(_)}
    }
  ), 
  NewColNames = List.Combine(
    List.Transform(
      {1 .. List.Max(Group[RowCount])}, 
      (a) => List.Transform({"Employee", "Age", "Nationality", "Salary"}, (b) => b & Text.From(a))
    )
  ), 
  SplitData = Table.SplitColumn(
    Table.RemoveColumns(Group, "RowCount"), 
    "Data", 
    Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), 
    NewColNames
  )
in
  SplitData

Power Query solution 7 for Transpose Departments into Row, proposed by Victor Wang:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Split = Table.SplitColumn(Source, "Age & Nationality & Salary", Splitter.SplitTextByDelimiter(", "), {"Age", "Nationality", "Salary"}),
 Cols = List.Skip(Table.ColumnNames(Split)),
 Group = Table.Group(Split, {"Dept"}, {{"all", each let t = Table.RemoveColumns(_, "Dept") in 
[ 
 rowCount = Text.From(Table.RowCount(t)),
 newCols = {"Dept"} & List.Combine(List.Transform({"1"..rowCount}, (a)=> List.Transform(Cols, (b)=> b & a ))),
 toRows = List.Combine(Table.ToRows(t))
]
[[toRows],[newCols]]
}}),
 getTables = Table.AddColumn(Group, "Custom", each hashtag#table([all][newCols], {{[Dept]} & [all][toRows]})),
 Combine = Table.Combine(getTables[Custom])
in
 Combine

Power Query solution 8 for Transpose Departments into Row, proposed by Dominic Walsh:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Dept"}, 
    {{"All", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}
  ), 
  Expand = Table.ExpandTableColumn(
    Group, 
    "All", 
    {"Employee", "Age & Nationality & Salary", "Index"}, 
    {"Employee", "Age & Nationality & Salary", "Index"}
  ), 
  Split = Table.SplitColumn(
    Expand, 
    "Age & Nationality & Salary", 
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
    {"Age", "Nationality", "Salary"}
  ), 
  Unpivot = Table.UnpivotOtherColumns(Split, {"Dept", "Index"}, "Attribute", "Value"), 
  Merge = Table.CombineColumns(
    Table.TransformColumnTypes(Unpivot, {{"Index", type text}}, "en-GB"), 
    {"Attribute", "Index"}, 
    Combiner.CombineTextByDelimiter("", QuoteStyle.None), 
    "Merged"
  ), 
  Pivot = Table.Pivot(Merge, List.Distinct(Merge[Merged]), "Merged", "Value")
in
  Pivot

Power Query solution 9 for Transpose Departments into Row, proposed by Felipe Perez Arevalo:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Grouped Rows" = Table.Group(
    Source, 
    {"Dept"}, 
    {
      {
        "Detail", 
        each 
          let
            step1 = Table.CombineColumns(
              _, 
              {"Employee", "Age & Nationality & Salary"}, 
              Combiner.CombineTextByDelimiter(",", QuoteStyle.None), 
              "Merged"
            ), 
            step2 = Table.RemoveColumns(step1, {"Dept"}), 
            step3 = Table.Transpose(step2), 
            step4 = Table.CombineColumns(
              step3, 
              Table.ColumnNames(step3), 
              Combiner.CombineTextByDelimiter(",", QuoteStyle.None), 
              "Merged"
            )
          in
            step4[Merged]{0}, 
        type text
      }, 
      {"Count", each List.Count([Dept]), Int64.Type}
    }
  ), 
  maxCount = List.Max(#"Grouped Rows"[Count]), 
  Columnhearders = List.Combine(
    List.Generate(
      () => [n = 1], 
      each [n] < maxCount + 1, 
      each [n = [n] + 1], 
      each {
        "Employee" & Text.From([n]), 
        "Age" & Text.From([n]), 
        "Nationality" & Text.From([n]), 
        "Salary" & Text.From([n])
      }
    )
  ), 
  SplitColumn = Table.RemoveColumns(
    Table.SplitColumn(
      #"Grouped Rows", 
      "Detail", 
      Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
      Columnhearders
    ), 
    {"Count"}
  )
in
  SplitColumn

Solving the challenge of Transpose Departments into Row with Excel

Excel solution 1 for Transpose Departments into Row, proposed by Bo Rydobon 🇹🇭:

=LET(z,A2:C11,a,TAKE(z,,1),REDUCE(HSTACK(A1,TOROW(TEXTSPLIT(B1&" & "&C1," & ")&SEQUENCE(MAX(COUNTIF(a,a))))),UNIQUE(a),
LAMBDA(c,v,IFNA(VSTACK(c,HSTACK(v,TEXTSPLIT(ARRAYTOTEXT(DROP(FILTER(z,a=v),,1)),", "))),""))))

Excel solution 2 for Transpose Departments into Row, proposed by Rick Rothstein:

=LET(j," & ",a,A2:A10,u,UNIQUE(a),s,SEQUENCE(MAX(COUNTIF(a,u))),t,TEXTSPLIT(TEXTJOIN("/",,MAP(u,LAMBDA(x,TEXTJOIN(",",,FILTER(B2:B10&", "&C2:C10,a=x))))),",","/"),VSTACK(HSTACK(A1,TEXTSPLIT(TEXTJOIN(j,,SUBSTITUTE(B1&j&C1,j,s&j)&s),j)),HSTACK(u,IFNA(IFERROR(0+t,t),""))))

Excel solution 3 for Transpose Departments into Row, proposed by محمد حلمي:

=LET(
r,A2:A11,
VSTACK(HSTACK(A1,
TOROW(TEXTSPLIT(B1& " & " &C1," & ")&
SEQUENCE(MAX(COUNTIF(r,r))))),
IFNA(DROP(REDUCE(0,UNIQUE(r),LAMBDA(a,d,LET(
f,FILTER(B2:C11,r=d),
VSTACK(a,
HSTACK(d,TEXTSPLIT(CONCAT(TAKE(f,,1)&", "&
DROP(f,,1)&", "),", ",,1)))))),1),"")))

Excel solution 4 for Transpose Departments into Row, proposed by Oscar Mendez Roca Farell:

=IFNA(LET(_u, UNIQUE(A2:A11), HSTACK( VSTACK("Dept",_u), REDUCE(TOROW(TEXTSPLIT(B1&" & "&C1, " & ") & SEQUENCE(COUNTA(_u))), _u, LAMBDA(i, x, VSTACK(i, LET(_m, FILTRAR(B2:C11, A2:A11=x), TEXTSPLIT(ARRAYTOTEXT(_m), ","))))))), "")

Excel solution 5 for Transpose Departments into Row, proposed by Duy Tùng:

=LET(a,A2:A11,REDUCE(HSTACK(A1,TOROW(TRIM(TEXTSPLIT(B1&"&"&C1,"&"))&SEQUENCE(MAX(COUNTIF(a,a))))),UNIQUE(a),LAMBDA(x,y,IFNA(VSTACK(x,HSTACK(y,TEXTSPLIT(TEXTJOIN(", ",,FILTER(B2:C11,A2:A11=y)),", "))),""))))

Excel solution 6 for Transpose Departments into Row, proposed by Sunny Baggu:

=LET(_tbl,HSTACK(A2:B11,DROP(REDUCE("",C2:C11,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,", ")))),1)),_dept,TAKE(_tbl,,1),_udept,UNIQUE(_dept),
IFNA(DROP(REDUCE("",_udept,LAMBDA(a,v,VSTACK(a,HSTACK(v,TOROW(FILTER(DROP(_tbl,,1),_dept=v)))))),1),""))

Excel solution 7 for Transpose Departments into Row, proposed by LEONARD OCHEA 🇷🇴:

=LET(d,A2:A11,e,B2:B11,f,C2:C11,u,UNIQUE(d),s,SEQUENCE(MAX(COUNTIF(d,d))),he,HSTACK("Dept",TOROW(HSTACK("Employee","Age","Nationality","Salary")&s)),ans,HSTACK(e,DROP(REDUCE("",f,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,", ")))),1)),mf,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,TOROW(FILTER(ans,d=y))))),1),VSTACK(he,HSTACK(u,IFERROR(mf,""))))

Excel solution 8 for Transpose Departments into Row, proposed by Julien Lacaze:

=LET(_h1,A1,_h2,HSTACK(B1,TEXTSPLIT(C1," & ")),
_dept,UNIQUE(A2:A11),_cols,MAX(COUNTIFS(A2:A11,_dept)),
_header,HSTACK(_h1,TOROW(_h2&SEQUENCE(_cols))),_datasX,TEXTSPLIT(TEXTJOIN("|",1,C2:C11),", ","|",,,""),
_datas,HSTACK(A2:A11,B2:B11,_datasX),
_resdatas,MAKEARRAY(ROWS(UNIQUE(A2:A11)),COLUMNS(_header),LAMBDA(r,c,IFERROR(IF(c=1,CHOOSEROWS(_dept,r),CHOOSECOLS(TOROW(FILTER(CHOOSECOLS(_datas,SEQUENCE(1,4,2)),CHOOSECOLS(_datas,1)=CHOOSEROWS(_dept,r))),c-1)),""))),
VSTACK(_header,_resdatas)
)

&&&

Solving the challenge of Transpose Departments into Row with Power Query

Solving the challenge of Transpose Departments into Row with Excel

Leave a Reply Cancel reply