Transpose People Table

Transpose the problem table into result table.

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

Solving the challenge of Transpose People Table with Power Query

Power Query solution 1 for Transpose People Table, proposed by Zoran Milokanović:

let
  Source = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Input"]}[Content]), 
  T = (_, i) =>
    let
      l = List.RemoveNulls(List.Combine(List.Alternate(_, 1, 1, i)))
    in
      List.Split(l & List.Repeat({null}, Number.Mod(List.Count(l), 2)), 2), 
  S = Table.FromRows(
    List.TransformMany(List.Zip({T(Source, 1), T(Source, 0)}), (i) => {i{0} & i{1}}, (i, o) => o), 
    {"Group1", "Group2", "Value1", "Value2"}
  )
in
  S

Power Query solution 2 for Transpose People Table, proposed by Kris Jaganah:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Combine = List.RemoveNulls(List.Combine(Table.ToRows(Source))), 
  ToTable = Table.FromList(Combine, Splitter.SplitByNothing()), 
  Title = Table.AddColumn(
    ToTable, 
    "Custom", 
    each if Value.Is(Value.FromText([Column1]), type number) then "Value" else "Group"
  ), 
  Group = Table.Group(Title, {"Custom"}, {"All", each _, type table [Column1 = any, Custom = text]}), 
  Idx = Table.AddColumn(Group, "Idx", each Table.AddIndexColumn([All], "Index", 1, 1)), 
  Xpand = Table.ExpandTableColumn(Idx, "Idx", {"Column1", "Index"}, {"Des", "Index"}), 
  TitleFinal = Table.AddColumn(
    Xpand, 
    "Title", 
    each if Number.IsOdd([Index]) then [Custom] & "1" else [Custom] & "2"
  ), 
  IdxRound = Table.TransformColumns(TitleFinal, {"Index", each Number.RoundUp(_ / 2)}), 
  Remove = Table.RemoveColumns(IdxRound, {"Custom", "All"}), 
  Pivot = Table.Pivot(Remove, List.Distinct(Remove[Title]), "Title", "Des"), 
  Remove1 = Table.RemoveColumns(Pivot, {"Index"})
in
  Remove1

Power Query solution 3 for Transpose People Table, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Zip = List.Zip(List.Split(Table.ToRows(Source), 2)), 
  Fx = (k) =>
    List.Transform(
      List.Split(List.RemoveNulls(List.Combine(Zip{k})), 2), 
      each if List.Count(_) = 1 then _ & {null} else _
    ), 
  Sol = Table.Combine(
    List.Transform(
      {0 .. List.Count(Fx(0)) - 1}, 
      each Table.FromRows({Fx(0){_} & Fx(1){_}}, {"Group1", "Group2", "Value1", "Value2"})
    )
  )
in
  Sol

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

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Zip = List.Zip(List.Split(Table.ToRows(Source), 2)), 
  Zip2 = List.Transform(
    List.Split(List.RemoveNulls(List.Combine(Zip{0})), 2), 
    each if List.Count(_) = 1 then _ & {null} else _
  ), 
  Zip3 = List.Transform(
    List.Split(List.RemoveNulls(List.Combine(Zip{1})), 2), 
    each if List.Count(_) = 1 then _ & {null} else _
  ), 
  Sol = Table.Combine(
    List.Transform(
      {0 .. List.Count(Zip2) - 1}, 
      each Table.FromRows({Zip2{_} & Zip3{_}}, {"Group1", "Group2", "Value1", "Value2"})
    )
  )
in
  Sol

Power Query solution 5 for Transpose People Table, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  res = [
    a = List.Combine(Table.ToRows(Fonte)), 
    b = List.Transform(
      List.Split(List.Select(a, each _ is text), 2), 
      each if List.Count(_) < 2 then _ & {null} else _
    ), 
    c = List.Transform(
      List.Split(List.Select(a, each _ is number), 2), 
      each if List.Count(_) < 2 then _ & {null} else _
    ), 
    d = Table.FromRows(
      List.Transform(List.Zip({b, c}), List.Combine), 
      {"Group1", "Group2", "Value1", "Value2"}
    )
  ][d]
in
  res

Power Query solution 6 for Transpose People Table, proposed by Alexis Olson:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  TransposeAndSplit = List.Split(Table.ToColumns(Table.Transpose(Source)), 2), 
  CombineIntoTable = Table.Combine(
    List.Transform(TransposeAndSplit, each Table.FromColumns(_, {"Group", "Value"}))
  ), 
  FilterNulls = Table.SelectRows(CombineIntoTable, each [Group] <> null), 
  Rows = Number.RoundUp(Table.RowCount(FilterNulls) / 2), 
  Answer = Table.FromColumns(
    List.Split(FilterNulls[Group], Rows) & List.Split(FilterNulls[Value], Rows), 
    {"Group1", "Group2", "Value1", "Value2"}
  )
in
  Answer

Power Query solution 7 for Transpose People Table, proposed by Ramiro Ayala Chávez:

let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  a      = Table.ToRows(Origen), 
  b      = List.RemoveNulls(List.Combine(List.Alternate(a, 1, 1, 1))), 
  c      = List.RemoveNulls(List.Combine(List.Alternate(a, 1, 1))), 
  d      = List.Alternate(b, 1, 1, 1), 
  e      = List.Alternate(b, 1, 1), 
  f      = List.Alternate(c, 1, 1, 1), 
  g      = List.Alternate(c, 1, 1), 
  Sol    = Table.FromColumns({d, e, f, g}, {"Group1", "Group2", "Value1", "Value2"})
in
  Sol

Power Query solution 8 for Transpose People Table, proposed by Eric Laforce:

let
  NGrp = 2, 
  CN = List.Combine(
    List.Accumulate(
      {1 .. NGrp}, 
      {{}, {}}, 
      (s, c) => {s{0} & {"Group" & Text.From(c)}, s{1} & {"Value" & Text.From(c)}}
    )
  ), 
  Source = Excel.CurrentWorkbook(){[Name = "tData138"]}[Content], 
  ToRows = List.Transform(Table.ToRows(Source), List.RemoveNulls), 
  All = List.Zip(
    {List.Combine(List.Alternate(ToRows, 1, 1, 1)), List.Combine(List.Alternate(ToRows, 1, 1, 0))}
  ), 
  Transform = List.Transform(
    List.Split(All, NGrp), 
    (_GVs as list) =>
      List.Combine(
        List.Accumulate(
          {0 .. NGrp - 1}, 
          {{}, {}}, 
          (s, c) => {s{0} & {try _GVs{c}{0} otherwise null}, s{1} & {try _GVs{c}{1} otherwise null}}
        )
      )
  ), 
  ToTable = Table.FromRows(Transform, CN)
in
  ToTable

Power Query solution 9 for Transpose People Table, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  List = List.Select(List.Combine(Table.ToColumns(S)), each _ <> null), 
  F = Table.Sort(
    Table.FromColumns(
      {List.Alternate(List, 1, 1, 1), List.Alternate(List, 1, 1)}, 
      {"Group", "Value"}
    ), 
    {{"Group", Order.Ascending}}
  ), 
  A = Table.AddColumn(F, "L", each Record.ToList(_)), 
  R = Table.SelectColumns(A, {"L"}), 
  E = Table.ExpandListColumn(R, "L"), 
  C = List.Split(E[L], 4), 
  D = Table.FromList(C, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
  E2 = Table.TransformColumns(
    D, 
    {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text}
  ), 
  S2 = Table.SplitColumn(
    E2, 
    "Column1", 
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
    {"Group1", "Value1", "Group2", "Value2"}
  ), 
  Fi = Table.ReorderColumns(S2, {"Group1", "Group2", "Value1", "Value2"})
in
  Fi

Power Query solution 10 for Transpose People Table, proposed by Rafael González B.:

let
  Source = Excel.CurrentWorkbook(){0}[Content], 
  TS = Table.Split(Source, 2), 
  TT = List.Transform(TS, each Table.Transpose(_)), 
  TC = Table.Combine(TT), 
  RB = Table.SelectRows(TC, each ([Column1] <> "")), 
  TS2 = Table.Split(RB, 1), 
  LT = List.Transform(
    TS2, 
    each 
      let
        a = Table.Pivot(_, List.Distinct(_[Column1]), "Column1", "Column2"), 
        b = Table.DemoteHeaders(a), 
        c = Table.Transpose(b)
      in
        c
  ), 
  TTL = Table.FromList(LT, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
  Index = Table.AddIndexColumn(TTL, "Index", 1, 1, Int64.Type), 
  TAC = Table.AddColumn(
    Index, 
    "Tbl", 
    each 
      let
        d = Number.IsOdd([Index]), 
        e = Table.RenameColumns, 
        f = e([Column1], {{"Column1", "Group1"}, {"Column2", "Value1"}}), 
        g = e([Column1], {{"Column1", "Group2"}, {"Column2", "Value2"}}), 
        h = if d then f else g
      in
        h
  )[Tbl], 
  FillUp = Table.FillUp(Table.Combine(TAC), {"Group2", "Value2"}), 
  Anw = Table.SelectRows(FillUp, each ([Value1] <> null))[[Group1], [Group2], [Value1], [Value2]]
in
  Anw

Power Query solution 11 for Transpose People Table, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Custom1 = Table.ToRows(Source), 
  Custom2 = List.Split(Custom1, 2), 
  Custom3 = List.Zip(Custom2), 
  Custom4 = List.Transform(Custom3, each List.RemoveNulls(List.Combine(_))), 
  Custom5 = Table.ToColumns(Table.AlternateRows(Table.FromColumns(Custom4), 1, 1, 1))
    & Table.ToColumns(Table.AlternateRows(Table.FromColumns(Custom4), 0, 1, 1)), 
  Custom6 = Table.FromColumns(Custom5, {"Group1", "Value1", "Group2", "Value2"}), 
  #"Reordered Columns" = Table.ReorderColumns(Custom6, {"Group1", "Group2", "Value1", "Value2"})
in
  #"Reordered Columns"

Power Query solution 12 for Transpose People Table, proposed by Dominic Walsh:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivot = Table.UnpivotOtherColumns(Source, {}, "Column", "Value"), 
  Type = Table.TransformColumnTypes(Unpivot, {{"Value", type text}}), 
  Group1 = List.Alternate(
    List.Buffer(Table.SelectRows(Type, each ([Value] >= "A"))[Value]), 
    1, 
    1, 
    1
  ), 
  Group2 = List.Alternate(
    List.Buffer(Table.SelectRows(Type, each ([Value] >= "A"))[Value]), 
    1, 
    1, 
    0
  ), 
  Value1 = List.Alternate(List.Buffer(Table.SelectRows(Type, each ([Value] < "A"))[Value]), 1, 1, 1), 
  Value2 = List.Alternate(List.Buffer(Table.SelectRows(Type, each ([Value] < "A"))[Value]), 1, 1, 0), 
  Result = Table.FromColumns(
    {Group1, Group2, Value1, Value2}, 
    {"Group1", "Group2", "Value1", "Value2"}
  )
in
  Result

Solving the challenge of Transpose People Table with Excel

Excel solution 1 for Transpose People Table, proposed by Rick Rothstein:

=LET(d,TOCOL(A2:F9,1),HSTACK(WRAPROWS(FILTER(d,ISTEXT(d)),2,""),WRAPROWS(FILTER(d,ISNUMBER(d)),2,"")))

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

=REDUCE(H1:K1,A2:F9,LAMBDA(a,d,
IF(ISEVEN(ROW(d))*ISODD(COLUMN(d))*(d>0),
VSTACK(a,TOROW(OFFSET(d,,,2,2))),a)))

Excel solution 3 for Transpose People Table, proposed by محمد حلمي:

=LET(m,TOCOL(A2:F9,1),HSTACK(WRAPROWS(FILTER(m,m>""),2,""),WRAPROWS(TOCOL(--m,2),2,"")))

Excel solution 4 for Transpose People Table, proposed by 🇰🇷 Taeyong Shin:

=LET(
 d, A2:F9,
 func, LAMBDA(fn, WRAPROWS(TOCOL(IFS(fn(d), d), 2), 2, "")),
 VSTACK(TOROW({"Group";"Value"} & {1,2}), HSTACK(func(ISTEXT), func(ISNUMBER)))
)

Excel solution 5 for Transpose People Table, proposed by Kris Jaganah:

=LET(a,A2:F9,b,WRAPROWS(TOCOL(IF(ISTEXT(a),a,1/0),3),2,""),c,WRAPROWS(TOCOL(IF(a="",1/0,a/1),3),2,""),d,SEQUENCE(,COLUMNS(b)),HSTACK(VSTACK("Group"&d,b),VSTACK("Value"&d,c)))

Excel solution 6 for Transpose People Table, proposed by Nikola Z Grujicic - Nikola Ž Grujičić:

=LET(m, SEQUENCE(ROWS(A2:F9)),n, FILTER(m, MOD(m,2)=0),l, FILTER(m, MOD(m,2)=1),o, CHOOSEROWS(A2:F9,n),a, CHOOSEROWS(A2:F9,l),u, TOCOL(o),v, FILTER(u, u<>0),w, TOCOL(a),x, FILTER(w, w<>0),HSTACK(WRAPROWS(x,2,""),WRAPROWS(v,2,"")))

Excel solution 7 for Transpose People Table, proposed by Oscar Mendez Roca Farell:

=LET(_t, TOCOL(A2:F9),_f, LAMBDA(i, WRAPROWS(TOCOL(IFS(i, _t), 3), 2, "")), HSTACK(_f(_t>""),_f(_t)))

Excel solution 8 for Transpose People Table, proposed by Duy Tùng:

=LET(a,A2:F9,W,WRAPROWS,IFNA(HSTACK(W(TOCOL(IFS(a>"",a),3),2),W(TOCOL(IFS(a<"",a),3),2)),""))

Excel solution 9 for Transpose People Table, proposed by Sunny Baggu:

=LET(
 _c, CODE(A2:F9) > 64,
 HSTACK(
 WRAPROWS(TOCOL(IF(_c, A2:F9, 1 / x), 3), 2, ""),
 WRAPROWS(TOCOL(IF(1 - _c, A2:F9, 1 / x), 3), 2, "")
 )
)

Excel solution 10 for Transpose People Table, proposed by Sunny Baggu:

=LET(
 _c, TOCOL(A2:F9, 3),
 HSTACK(
 WRAPROWS(FILTER(_c, ISERR(--_c)), 2, ""),
 WRAPROWS(FILTER(_c, ISNUMBER(--_c)), 2, "")
 )
)

Excel solution 11 for Transpose People Table, proposed by LEONARD OCHEA 🇷🇴:

=LET(s,SEQUENCE(4,,1,2),F,LAMBDA(x,y,WRAPROWS(TOCOL(CHOOSEROWS(x,y),3),2,"")),HSTACK(F(A2:F9,s),F(A2:F9,s+1)))

Excel solution 12 for Transpose People Table, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:

=LET(k;LET(i;TOCOL(A2:F9;1);FILTER(i;ISNUMBER(i)));h;LET(i;TOCOL(A2:F9;1);FILTER(i;ISTEXT(i)));HSTACK(FILTER(h;ISODD(SEQUENCE(COUNTA(h))));FILTER(h;ISEVEN(SEQUENCE(COUNTA(h))));FILTER(k;ISODD(SEQUENCE(COUNTA(k))));FILTER(k;ISEVEN(SEQUENCE(COUNTA(k))))))

Solving the challenge of Transpose People Table with R

R solution 1 for Transpose People Table, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
input = read_excel("PQ_Challenge_138.xlsx", range = "A1:F9")
test = read_excel("PQ_Challenge_138.xlsx", range = "H1:K10")
result <- input %>%
 group_by(group_id = (row_number() - 1) %/% 2) %>%
 group_map(~ .x) %>%
 set_names(seq_along(.))
a1 = result %>%
 map(., ~ as_tibble(t(.))) %>%
 bind_rows() %>%
 drop_na() %>%
 add_row(V1 = NA_character_, V2 = NA_character_) 
a2_L = matrix(a1$V1, ncol=2, byrow = TRUE)
a2_D = matrix&(a1$V2, ncol=2, byrow = TRUE)
a2 = bind_cols(a2_L, a2_D) %>%
 as_tibble() %>%
 rename(Group1 = ...1, Group2 = ...2, Value1 = ...3, Value2 = ...4) %>%
 mutate(Value1 = as.numeric(Value1),
 Value2 = as.numeric(Value2))

Solving the challenge of Transpose People Table with Power Query

Solving the challenge of Transpose People Table with Excel

Solving the challenge of Transpose People Table with R

Leave a Reply Cancel reply