Transpose Team Problem Table

Transpose the problem table into result table. Sorting to be done on Team column

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

Solving the challenge of Transpose Team Problem Table with Power Query

Power Query solution 1 for Transpose Team Problem Table, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  TP = Table.Group(
    Table.Transpose(Table.DemoteHeaders(Source)), 
    "Column1", 
    {
      "T", 
      each Table.ExpandListColumn(
        Table.TransformColumns(
          Table.SelectRows(
            Table.Skip(
              Table.Transpose(
                Table.FirstN(Table.FillUp(_, Table.ColumnNames(_)), 2), 
                {"Dept", "Team"}
              )
            ), 
            each [Dept] <> null
          ), 
          {"Team", each Text.Split(Text.From(_), ", ")}
        ), 
        "Team"
      )
    }, 
    0, 
    (b, e) => Number.From(Text.End(e, 1) < "A")
  ), 
  Ans = Table.RenameColumns(
    Table.ExpandTableColumn(TP, "T", {"Dept", "Team"}), 
    {"Column1", "Group"}
  )
in
  Ans

Power Query solution 2 for Transpose Team Problem Table, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  Flatten = Table.FromColumns(
    List.Transform(Table.ToColumns(Source), each {List.RemoveNulls(_){0}}), 
    Table.ColumnNames(Source)
  ), 
  Unpivot = Table.UnpivotOtherColumns(Flatten, {}, "A", "Team"), 
  GroupDept = Table.SelectRows(
    Table.FillDown(
      Table.SplitColumn(
        Unpivot, 
        "A", 
        each 
          let
            f = Text.StartsWith(_, "G")
          in
            {if f then _ else null, if f then null else _}, 
        {"Group", "Dept"}
      ), 
      {"Group"}
    ), 
    each ([Dept] <> null)
  ), 
  Team = Table.ExpandListColumn(
    Table.ReplaceValue(GroupDept, null, null, (x, y, z) => Text.Split(Text.From(x), ", "), {"Team"}), 
    "Team"
  )
in
  Team

Power Query solution 3 for Transpose Team Problem Table, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Unpivot = Table.UnpivotOtherColumns(Source, {}, "Group", "Dept"), 
  Index = Table.AddIndexColumn(Unpivot, "Index", 1), 
  Team = Table.AddColumn(
    Index, 
    "Team", 
    each 
      if Text.Length(Text.Select([Group], {"0" .. "9"})) > 0 then
        Text.Split(Text.From(Unpivot[Dept]{[Index]}), ", ")
      else
        null
  ), 
  Filter = Table.SelectRows(Team, each [Team] <> null), 
  Expand = Table.ExpandListColumn(Filter, "Team"), 
  Sort = Table.Sort(Expand, each Number.From([Team])), 
  Return = Table.RemoveColumns(Sort, "Index")
in
  Return

Power Query solution 4 for Transpose Team Problem Table, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivot = Table.Unpivot(Source, Table.ColumnNames(Source), "Group", "Dept"), 
  Select = Table.SelectRows(
    Table.FromColumns(
      Table.ToColumns(Unpivot) & {List.Skip(Unpivot[Dept]) & {null}}, 
      Table.ColumnNames(Unpivot) & {"Team"}
    ), 
    each Text.Contains([Group], "G")
  ), 
  Team = Table.TransformColumns(
    Select, 
    {
      "Team", 
      each 
        let
          a = try Text.Split(_, ", ") otherwise {_}, 
          b = List.Transform(a, Number.From)
        in
          b
    }
  ), 
  Sort = Table.Sort(Table.ExpandListColumn(Team, "Team"), {{"Team", Order.Ascending}})
in
  Sort

Power Query solution 5 for Transpose Team Problem Table, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivot = Table.Unpivot(Source, Table.ColumnNames(Source), "Dept", "Team"), 
  Group = Table.AddColumn(
    Unpivot, 
    "Group", 
    each if Text.Contains([Dept], "G") then [Dept] else null
  ), 
  FD = Table.SelectRows(Table.FillDown(Group, {"Group"}), each not Text.Contains([Dept], "G")), 
  Expand = Table.Sort(
    Table.ExpandListColumn(
      Table.TransformColumns(
        FD, 
        {
          "Team", 
          each 
            let
              a = try Text.Split(_, ", ") otherwise {_}, 
              b = List.Transform(a, Number.From)
            in
              b
        }
      ), 
      "Team"
    ), 
    "Team"
  ), 
  Order = Table.SelectColumns(Expand, {"Group", "Dept", "Team"})
in
  Order

Power Query solution 6 for Transpose Team Problem Table, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  col = Table.UnpivotOtherColumns(Fonte, {}, "Atributo", "Dept"), 
  tab = Table.TransformColumns(
    Table.SelectRows(col, each not Text.StartsWith([Atributo], "G")), 
    {
      {
        "Dept", 
        each List.Transform(
          Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)(Text.From(_)), 
          Number.From
        )
      }
    }
  ), 
  fil = Table.SelectRows(col, each Text.StartsWith([Atributo], "G")), 
  add = Table.AddColumn(fil, "Team", (x) => tab{[Atributo = x[Dept]]}[Dept]), 
  res = Table.Sort(Table.ExpandListColumn(add, "Team"), {"Team"})
in
  res

Power Query solution 7 for Transpose Team Problem Table, proposed by Hussein SATOUR:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  TransTab = Table.Transpose(Source), 
  AddG = Table.AddColumn(
    TransTab, 
    "Group", 
    each if Text.Start([Column1], 1) = "G" then [Column1] else null
  ), 
  FilledD = Table.FillDown(AddG, {"Group"}), 
  NotG = Table.SelectRows(FilledD, each not Text.StartsWith([Column1], "G")), 
  MergValues = Table.CombineColumns(
    NotG, 
    {"Column2", "Column3", "Column4"}, 
    Combiner.CombineTextByDelimiter("", QuoteStyle.None), 
    "Team"
  ), 
  SplitValues = Table.ExpandListColumn(
    Table.TransformColumns(
      MergValues, 
      {
        {
          "Team", 
          Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "Team"
  ), 
  RenamCol = Table.RenameColumns(SplitValues, {{"Column1", "Dept"}})
in
  RenamCol

Power Query solution 8 for Transpose Team Problem Table, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  S1 = Table.Transpose(Source), 
  S2 = Table.CombineColumns(
    Table.TransformColumnTypes(S1, {{"Column2", type text}}, "en-IN"), 
    {"Column2", "Column3", "Column4"}, 
    Combiner.CombineTextByDelimiter("", QuoteStyle.None), 
    "Merged"
  ), 
  S3 = Table.AddColumn(
    S2, 
    "Custom", 
    each if List.Contains({"G1", "G2", "G3"}, [Column1]) then [Column1] else null
  ), 
  S4 = Table.FillDown(S3, {"Custom"}), 
  S5 = Table.SplitColumn(
    S4, 
    "Merged", 
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
    {"Merged.1", "Merged.2", "Merged.3"}
  ), 
  S6 = Table.TransformColumnTypes(
    S5, 
    {{"Merged.1", type text}, {"Merged.2", Int64.Type}, {"Merged.3", Int64.Type}}
  ), 
  S7 = Table.TransformColumnTypes(S6, {{"Merged.1", Int64.Type}}), 
  S8 = Table.RemoveRowsWithErrors(S7, {"Merged.1"}), 
  S9 = Table.Group(
    S8, 
    {"Column1", "Custom"}, 
    {{"Count", each Table.ToRows(_[[Merged.1], [Merged.2], [Merged.3]])}}
  ), 
  S10 = Table.ExpandListColumn(S9, "Count"), 
  S11 = Table.ExpandListColumn(S10, "Count"), 
  S12 = Table.SelectRows(S11, each ([Count] <> null)), 
  S13 = Table.RenameColumns(S12, {{"Custom", "Group"}, {"Column1", "Dept"}, {"Count", "Team"}}), 
  S14 = Table.ReorderColumns(S13, {"Group", "Dept", "Team"})
in
  S14

Power Query solution 9 for Transpose Team Problem Table, proposed by Henriette Hamer:

let
 Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
 TT = Table.Transpose(Source),
 AC = Table.AddColumn(TT, "G", each if Text.Middle([Column1],0,1) = "G" then [Column1] else null),
 FD = Table.FillDown(AC,{"G"}),
 FR = Table.SelectRows(FD, each not Text.StartsWith([Column1], "G")),
 IMC = Table.AddColumn(FR, "Merged", each Text.Combine({Text.From([Column2], "nl-NL"), [Column3], [Column4]}, ""), type text),
 RmC1 = Table.RemoveColumns(IMC,{"Column2", "Column3", "Column4"}),
 RV = Table.ReplaceValue(RmC1," ","",Replacer.ReplaceText,{"Merged"}),
 SCbD = Table.SplitColumn(RV, "Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3"}),
 UC = Table.UnpivotOtherColumns(SCbD, {"Column1", "G"}, "Attribute", "Value"),
 RmC2 = Table.RemoveColumns(UC,{"Attribute"}),
 RoC = Table.ReorderColumns(RmC2,{"G", "Column1", "Value"}),
 RnC = Table.RenameColumns(RoC,{{"G", "Group"}, {"Column1", "Dept"}, {"Value", "Team"}}),
 #"Changed Type" = Table.TransformColumnTypes(RnC,{{"Team", Int64.Type}})
in
 #"Changed Type"

Fails the moment there is a department that starts with a "G" and when there are more than 3 departments per group and probably also when there are more then 3 teams.

Power Query solution 10 for Transpose Team Problem Table, proposed by Obi E, MPH:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table4"]}[Content], 
  TT = Table.Transpose(Source), 
  AC = Table.AddColumn(TT, "G", each if Text.Middle([Column1], 0, 1) = "G" then [Column1] else null), 
  FD = Table.FillDown(AC, {"G"}), 
  FR = Table.SelectRows(FD, each not Text.StartsWith([Column1], "G")), 
  IMC = Table.AddColumn(
    FR, 
    "Merged", 
    each Text.Combine({Text.From([Column2], "nl-NL"), [Column3], [Column4]}, ""), 
    type text
  ), 
  RmC1 = Table.RemoveColumns(IMC, {"Column2", "Column3", "Column4"}), 
  RV = Table.ReplaceValue(RmC1, " ", "", Replacer.ReplaceText, {"Merged"}), 
  SCbD = Table.SplitColumn(
    RV, 
    "Merged", 
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
    {"Merged.1", "Merged.2", "Merged.3"}
  ), 
  UC = Table.UnpivotOtherColumns(SCbD, {"Column1", "G"}, "Attribute", "Value"), 
  RmC2 = Table.RemoveColumns(UC, {"Attribute"}), 
  RoC = Table.ReorderColumns(RmC2, {"G", "Column1", "Value"}), 
  RnC = Table.RenameColumns(RoC, {{"G", "Group"}, {"Column1", "Dept"}, {"Value", "Team"}}), 
  #"Changed Type" = Table.TransformColumnTypes(RnC, {{"Team", Int64.Type}})
in
  #"Changed Type"

Solving the challenge of Transpose Team Problem Table with Excel

Excel solution 1 for Transpose Team Problem Table, proposed by Bo Rydobon 🇹🇭:

=REDUCE({"Group","Dept","Team"},A1:J1,LAMBDA(a,v,LET(z,A2:J4,c,XMATCH(v,A1:J1),
IF(RIGHT(v)<"a",VSTACK(a,IFNA(HSTACK(v,DROP(REDUCE(0,SEQUENCE(ROWS(z)),
LAMBDA(b,r,LET(d,INDEX(z,r,c),IF(d="",b,VSTACK(b,IFNA(HSTACK(d,--TEXTSPLIT(@TOCOL(DROP(INDEX(z,r,),,c),3),,", ")),d)))))),1)),v)),a))))

Excel solution 2 for Transpose Team Problem Table, proposed by محمد حلمي:

=LET(r,A1:J4,c,COLUMN(r),
e,MATCH(c,FIND("G",A1:J1)*c),
REDUCE({"Group","Dept","Team"},UNIQUE(e,1),LAMBDA(a,d,LET(
i,FILTER(r,d=e),
s,--TEXTSPLIT(CONCAT(DROP(i,1,1)&"/"),",","/",1),
VSTACK(a,IFNA(HSTACK(@i,
TOCOL(IF(s,DROP(TAKE(i,,1),1)),2),TOCOL(s,2)),@i))))))

Excel solution 3 for Transpose Team Problem Table, proposed by Oscar Mendez Roca Farell:

=TEXTSPLIT(TEXTJOIN(",", , LET(_f,FILTER(A1:J4&"",LEFT(A1:J1)="G"), REDUCE("",SEQUENCE(ROWS(_f)-1), LAMBDA(i, x, LET(_d, DROP(_f,1),_c,INDEX(_d, ,x), VSTACK(i, INDEX(_f,1, x)&"|"&DROP(REDUCE("", SEQUENCE(SUM(--(_c>""))), LAMBDA(j, y, LET(_m,CHOOSECOLS(A2:J4&"", TOCOL(XMATCH(_c,A1:J1), 2)), VSTACK(j, INDEX(_c, y)&"|"&TEXTSPLIT(TEXTJOIN("", , INDEX(_m, y, )), , ", "))))), 1))))))), "|", ",")

Excel solution 4 for Transpose Team Problem Table, proposed by Sunny Baggu:

=LET(
 _t, WRAPROWS(TOCOL(A2:J4, 3), 2),
 _G, MAP(TAKE(_t, , 1), LAMBDA(x, FILTER(A1:J1, BYCOL(N(A2:J4 = x), LAMBDA(a, SUM(a)))))),
 _rng, SORT(HSTACK(_G, _t)),
 _tbl, DROP(
 REDUCE(
 "",
 SEQUENCE(ROWS(_rng)),
 LAMBDA(x, y,
 VSTACK(
 x,
 IFNA(
 DROP(
 REDUCE(
 "",
 SEQUENCE(COLUMNS(_rng)),
 LAMBDA(a, v, HSTACK(a, TEXTSPLIT(INDEX(INDEX(_rng, y, ), v), , ", ")))
 ),
 ,
 1
 ),
 ""
 )
 )
 )
 ),
 1
 ),
 TRANSPOSE(SCAN("", TRANSPOSE(_tbl), LAMBDA(a, v, IF(v = "", a, v))))
)

Excel solution 5 for Transpose Team Problem Table, proposed by LEONARD OCHEA 🇷🇴:

=LET(t,A1:J4,s,SCAN(0,"G"<>LEFT(INDEX(t,1,)),LAMBDA(a,b, IF(b,a+b,0))),d,COLUMN(t),f,INDEX(t,1,d-s),g,INDEX(t,1,d), h,INDEX(t,s+1,d),i,REDUCE(,h,LAMBDA(a,b,HSTACK(a,IF(b<>"",TOCOL(TEXTSPLIT(b,", ")),"")))),j,IF(s,f&"-"&g&"-"&i,NA()), k,TOCOL(TRANSPOSE(j),3),VSTACK(HSTACK("Group","Dept","Team"),TEXTSPLIT(TEXTJOIN("@",,k),"-","@")))

Excel solution 6 for Transpose Team Problem Table, proposed by Md. Zohurul Islam:

=LET(u,A1:J1,v,A2:J4,hdr,HSTACK("Group","Dept","Team"),
w,SCAN(,IF(LEFT(u)="G",u,""),LAMBDA(x,y,IF(y="",x,y))),
z,REDUCE(hdr,TOCOL(UNIQUE(w,1)),LAMBDA(x,y,LET(
a,BYROW(WRAPROWS(TOCOL(FILTER(v,w=y),3),2),ARRAYTOTEXT),
b,DROP(REDUCE("",a,LAMBDA(p,q,LET(j,TEXTSPLIT(q,", "),k,TAKE(j,,1),m,VSTACK(p,IFNA(HSTACK(k,TOCOL(--DROP(j,,1))),k)),m))),1),
c,IFNA(HSTACK(y,b),y),
VSTACK(x,c)
))),
z)

Excel solution 7 for Transpose Team Problem Table, proposed by Asheesh Pahwa:

=LET(hea,A1:J1,arr,A2:J4,whl,
A1:J4,fil,TRANSPOSE(FILTER(hea,NOT(ISNUMBER(--RIGHT(hea,1))))),n,MAP(fil,LAMBDA(x,LET(a,INDEX(arr,,MATCH(x,hea,0)),
FILTER(a,a<>0)))),c,TEXTJOIN("|",,n),d,TEXTSPLIT(c,", ","|",,,), e,TOCOL(d&"-"&fil), f,HSTACK(TEXTAFTER(e,"-"),
TEXTBEFORE(e,"-")), g,FILTER(f,TAKE(f,,-1)<>""),h,FILTER(TRANSPOSE(whl), ISNUMBER(-- RIGHT(TAKE(TRANSPOSE(whl),,1),1))),i,TOCOL(TAKE(h,,1)&"-"&TAKE(h,,-COLUMNS(h)+1)),j,HSTACK(TEXTAFTER(i,"-"), TEXTSPLIT(i,"-")),k,XLOOKUP(TAKE(g,,1),TAKE(j,,1),TAKE(j,,-1)), HSTACK(k,g))

Excel solution 8 for Transpose Team Problem Table, proposed by Md Ismail Hosen:

=LAMBDA(Data, LET(Header, CHOOSEROWS(Data, 1), ExceptHeader, DROP(Data, 1), GroupCols, FILTER(Header, LEFT(Header, 1) = "G"), fx_ForGroup, LAMBDA(GroupName, LET(ColData, CHOOSECOLS(ExceptHeader, XMATCH(GroupName, Header, 0)), DeptTeamData, DROP(REDUCE("", ColData, LAMBDA(a,v, IF(v = "", a, VSTACK(a, LET(DeptCol, XMATCH(v, Header, 0), DeptColData, CHOOSECOLS(ExceptHeader, DeptCol), Teams, TRANSPOSE(TEXTSPLIT(@FILTER(DeptColData, DeptColData <> ""), ", ")) * 1, Result, HSTACK(EXPAND(v, ROWS(Teams), 1, v), Teams), Result))))), 1), Result, HSTACK(EXPAND(GroupName, ROWS(DeptTeamData), , GroupName), DeptTeamData), Result)), OutputHeader, {"Group","Dept","Team"}, Result, REDUCE(OutputHeader, GroupCols, LAMBDA(a,v, VSTACK(a, fx_ForGroup(v)))), Result))(A1:J4)

Excel solution 9 for Transpose Team Problem Table, proposed by Narayanan J 🇮🇳:

=LET(tbl,A1:J4,hd,TAKE(tbl,1),dt,DROP(tbl,1)&"",rpt,LAMBDA(h,d,DROP(TEXTSPLIT(REPT(TEXTJOIN(",",1,h)&"|",d),",","|"),-1)),dpt,rpt(hd,ROWS(dt)),grp,rpt(SCAN("",hd,LAMBDA(a,h,IF(LEFT(h)="G",h,a))),ROWS(dt)),splt,LAMBDA(a,b,c,TEXTJOIN(",",TRUE,a&":"&&b&":"&TEXTSPLIT(c,,", "))&","),ar,MAP(grp,dpt,dt,LAMBDA(g,dp,d,IFS(d="",NA(),g=dp,NA(),TRUE,splt(g,dp,d)))),DROP(TEXTSPLIT(TEXTJOIN(,0,TOCOL(ar,3)),":",","),-1))

Excel solution 10 for Transpose Team Problem Table, proposed by Narayanan J 🇮🇳:

=LET(tbl,INPUT,hdng,TOCOL(INPUT[hashtag#Headers]),hs,SEQUENCE(ROWS(hdng)),hss,SCAN(0,hdng,LAMBDA(a,h,IFS(LEN(h)>2,a,LEFT(h)<>"G",a,TRUE,MATCH(h,hdng,0)))),arr,MAKEARRAY(ROWS(tbl),ROWS(hdng),LAMBDA(r,c,LET(pos,1+(r-1)*c+(c-1),grp,INDEX(hss,c),dpt,INDEX(hs,c),data,""&INDEX(tbl,r,c),op,INDEX(hdng,grp)&":"&INDEX(hdng,dpt)&":"&data,IFS(data="","",grp=dpt,"",TRUE,SUBSTITUTE(op," ",""))))),col,TOCOL(arr,TRUE,TRUE),lst,FILTER(col,col<>"",""),cont,LAMBDA(txt,LET(c,TEXTSPLIT(txt,,":"),TEXTJOIN(",",TRUE,INDEX(c,1)&":"&INDEX(c,2)&":"&TRANSPOSE(DROP(TEXTSPLIT(txt,",",":"),2))))),a,TEXTSPLIT(TEXTJOIN(",",TRUE,MAP(lst,cont)),":",","),IFERROR(VALUE(a),a))

Solving the challenge of Transpose Team Problem Table with Power Query

Solving the challenge of Transpose Team Problem Table with Excel

Leave a Reply Cancel reply