Home » Transform Input to Output

Transform Input to Output

This time problem statement is short. You need to transform input table into result table.

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

Solving the challenge of Transform Input to Output with Power Query

Power Query solution 1 for Transform Input to Output, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  HD = Table.Group(
    Source, 
    "Data1", 
    {"A", each _}, 
    0, 
    (b, e) => Number.From(Text.Start(e, 1) <> "G")
  ), 
  Combine = Table.Combine(
    List.Combine(
      Table.Group(
        HD, 
        "Data1", 
        {
          "B", 
          each List.Transform(
            List.Skip([A]), 
            (t) => Table.PromoteHeaders(Table.Transpose([A]{0} & t))
          )
        }, 
        0, 
        (b, e) => Number.From(e = "Hall")
      )[B]
    )
  )
in
  Combine
Power Query solution 2 for Transform Input to Output, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "InputTable"]}[Content], 
  AddedIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), 
  AddedRow = Table.AddColumn(
    AddedIndex, 
    "Row", 
    each if [Data1] = "Date" then [Index] else if [Data1] = "Hall" then "X" else null
  ), 
  FilledDownRow = Table.FillDown(AddedRow, {"Row"}), 
  ReplacedValue = Table.ReplaceValue(FilledDownRow, "X", null, Replacer.ReplaceValue, {"Row"}), 
  FilledUpRow = Table.FillUp(ReplacedValue, {"Row"}), 
  RemovedIndex = Table.RemoveColumns(FilledUpRow, {"Index"}), 
  PivotedData1 = Table.Pivot(RemovedIndex, List.Distinct(RemovedIndex[Data1]), "Data1", "Data2"), 
  FilledDownHall = Table.FillDown(PivotedData1, {"Hall"}), 
  RemovedRow = Table.RemoveColumns(FilledDownHall, {"Row"}), 
  FormatDate = Table.TransformColumnTypes(RemovedRow, {{"Date", type date}})
in
  FormatDate
Power Query solution 3 for Transform Input to Output, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Hall = Table.AddColumn(Source, "Hall", each if [Data1] = "Hall" then [Data2] else null), 
  Date = Table.AddColumn(Hall, "Date", each if [Data1] = "Date" then [Data2] else null), 
  Filled = Table.FillDown(Date, {"Hall", "Date"}), 
  Filtered = Table.SelectRows(Filled, each [Data2] <> [Hall] and [Data2] <> [Date]), 
  Group = Table.Group(Filtered, {"Hall", "Date"}, {"Group", each Record.FromList([Data2], [Data1])}), 
  Header = Record.FieldNames(List.Max(Group[Group], null, Record.FieldCount)), 
  Return = Table.ExpandRecordColumn(Group, "Group", Header)
in
  Return
Power Query solution 4 for Transform Input to Output, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Added1 = Table.AddColumn(
    Source, 
    "Hall", 
    each if Text.Contains([Data1], "Hall") then [Data2] else null
  ), 
  Added2 = Table.AddColumn(
    Added1, 
    "Date", 
    each if Value.Type([Data2]) = type datetime then Date.From([Data2]) else null
  ), 
  FD = Table.FillDown(Added2, {"Date", "Hall"}), 
  Filter = Table.SelectRows(FD, each ([Data1] <> "Date" and [Data1] <> "Hall")), 
  Sol = Table.Pivot(Filter, List.Distinct(Filter[Data1]), "Data1", "Data2")
in
  Sol
Power Query solution 5 for Transform Input to Output, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  ind = Table.AddIndexColumn(Fonte, "Índice", 0, 1, Int64.Type), 
  col = Table.Pivot(ind, List.Distinct(ind[Data1]), "Data1", "Data2"), 
  pb = Table.FillDown(col, {"Hall", "Date"}), 
  gp = Table.Group(
    pb, 
    {"Hall", "Date"}, 
    {
      {
        "Contagem", 
        each [
          a = Table.SelectColumns(
            _, 
            List.Select(Table.ColumnNames(pb), each Text.Start(_, 5) = "Guest")
          ), 
          b = List.RemoveNulls(List.Combine(Table.ToColumns(a))), 
          c = Text.Combine(List.Transform(b, Text.From), "|"), 
          d = Table.ColumnCount(a)
        ][c]
      }
    }
  ), 
  filt = Table.SelectRows(gp, each ([Contagem] <> "")), 
  n_col = Table.ColumnNames(
    Table.SelectColumns(pb, List.Select(Table.ColumnNames(pb), each Text.Start(_, 5) = "Guest"))
  ), 
  res = Table.SplitColumn(
    filt, 
    "Contagem", 
    Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
    n_col
  )
in
  res
Power Query solution 6 for Transform Input to Output, proposed by Bhavya Gupta: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  G_1 = Table.Group(
    Source, 
    {"Data1"}, 
    {{"All", each Record.FromList([Data2], [Data1])}}, 
    0, 
    (x, y) => Number.From(y[Data1] = "Date" or y[Data1] = "Hall")
  ), 
  G_2 = List.Combine(
    Table.Group(
      G_1, 
      {"Data1"}, 
      {{"All", each List.Transform(List.Skip([All]), (f) => [All]{0} & f)}}, 
      0, 
      (a, b) => Number.From(b[Data1] = "Hall")
    )[All]
  ), 
  Final = Table.FromRecords(G_2, Record.FieldNames(Record.Combine(G_2)), MissingField.UseNull)
in
  Final
Power Query solution 7 for Transform Input to Output, proposed by Eric Laforce: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tData55"]}[Content], 
  Add_Hall = Table.AddColumn(Source, "Hall", each if ([Data1] = "Hall") then [Data2] else null), 
  Add_Date = Table.AddColumn(Add_Hall, "Date", each if ([Data1] = "Date") then [Data2] else null), 
  FillDown = Table.FillDown(Add_Date, {"Hall", "Date"}), 
  Filter = Table.SelectRows(FillDown, each ([Data1] <> "Date" and [Data1] <> "Hall")), 
  AllGuests = List.Distinct(Filter[Data1]), 
  Group = Table.Group(
    Filter, 
    {"Hall", "Date"}, 
    {{"Data", each Table.Pivot(_[[Data1], [Data2]], List.Distinct(_[Data1]), "Data1", "Data2")}}
  ), 
  Expand = Table.ExpandTableColumn(Group, "Data", AllGuests, AllGuests)
in
  Expand
Power Query solution 8 for Transform Input to Output, proposed by Sandeep Marwal: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Grouped Rows" = Table.Group(
    Source, 
    {"Data1"}, 
    {
      {
        "Count", 
        each [
          #"Grouped Rows1" = Table.Group(
            _, 
            {"Data1"}, 
            {{"Count", each Table.PromoteHeaders(Table.Transpose(_))}}, 
            0, 
            (c, n) => Number.From(n[Data1] = "Date")
          ), 
          Custom1 = Table.Combine(#"Grouped Rows1"[Count]), 
          #"Filled Down" = Table.FillDown(Custom1, {"Hall"}), 
          #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Date] <> null))
        ][#"Filtered Rows"]
      }
    }, 
    0, 
    (c, n) => Number.From(n[Data1] = "Hall")
  )[[Count]], 
  #"Expanded Count" = Table.ExpandTableColumn(
    #"Grouped Rows", 
    "Count", 
    {"Hall", "Date", "Guest1", "Guest2", "Guest3", "Guest4"}
  ), 
  #"Changed Type" = Table.TransformColumnTypes(#"Expanded Count", {{"Date", type date}})
in
  #"Changed Type"
Power Query solution 9 for Transform Input to Output, proposed by Sue Bayes: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), 
  Set = Table.FillDown(
    Table.AddColumn(Index, "Set", each if [Data1] = "Hall" then [Data2] else null), 
    {"Set"}
  ), 
  GrpLogic = Table.FillDown(
    Table.AddColumn(
      Set, 
      "Set2", 
      each if [Data1] = "Hall" then [Index] + 1 else if [Data1] = "Date" then [Index] else null
    ), 
    {"Set2"}
  ), 
  Grp = Table.Group(
    GrpLogic, 
    {"Set", "Set2"}, 
    {{"All", each _, type table [Data1 = text, Data2 = any]}}
  ), 
  AddCols = Table.AddColumn(
    Grp, 
    "Answer", 
    each 
      let
        Select = Table.SelectColumns([All], {"Data1", "Data2", "Set"}), 
        Pivot  = Table.Pivot(Select, List.Distinct(Select[Data1]), "Data1", "Data2")
      in
        Pivot
  )[Answer], 
  ToTable = Table.FromList(AddCols, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
  Expand = Table.ExpandTableColumn(
    ToTable, 
    "Column1", 
    {"Set", "Date", "Guest1", "Guest2", "Guest3", "Guest4"}, 
    {"Hall", "Date", "Guest1", "Guest2", "Guest3", "Guest4"}
  ), 
  Type = Table.TransformColumnTypes(Expand, {{"Date", type date}})
in
  Type

Solving the challenge of Transform Input to Output with Excel

Excel solution 1 for Transform Input to Output, proposed by Bo Rydobon 🇹🇭: 
=LET(a,A2:A16,b,B2:B16,REDUCE(TOROW(UNIQUE(a)),FILTER(SEQUENCE(ROWS(b)),N(+b)),LAMBDA(c,n,
IFNA(VSTACK(c,HSTACK(XLOOKUP("Hall",TAKE(a,n),TAKE(b,n),,,-1),
TOROW(TAKE(DROP(b,n-1),IFNA(XMATCH(0,N(LEFT(DROP(a,n))="g")),n))))),""))))
Excel solution 2 for Transform Input to Output, proposed by Bo Rydobon 🇹🇭: 
=LET(z,A2:B16,r,REDUCE(TOROW(UNIQUE(INDEX(z,,1))),SEQUENCE(ROWS(z)),LAMBDA(c,n,
LET(a,INDEX(z,n,1),b,INDEX(z,n,2),y,TAKE(c,-1),
IFNA(VSTACK(c,HSTACK(IF(a="Hall",b,TAKE(y,,1)),IF(a="date",TOROW(TAKE(DROP(z,n-1,1),XMATCH(0,N(LEFT(VSTACK(DROP(z,n,-1),0))="g")))),0))),"")))),
FILTER(r,INDEX(r,,2)>0))
Excel solution 3 for Transform Input to Output, proposed by Bo Rydobon 🇹🇭: 
=LET(a,A2:A16,b,B2:B16,h,SCAN(0,SEQUENCE(ROWS(b)),LAMBDA(c,n,IF(INDEX(a,n)="Hall",INDEX(b,n),c))),d,SCAN(0,b,LAMBDA(c,v,IF(N(v),v,c))),
hd,HSTACK(h,d),uh,UNIQUE(FILTER(hd,a<>"hall")),REDUCE(TOROW(UNIQUE(a)),SEQUENCE(ROWS(uh)),LAMBDA(c,n,IFNA(VSTACK(c,HSTACK(INDEX(uh,n,1),TOROW(FILTER(b,h&d=CONCAT(INDEX(uh,n,)))))),""))))
Excel solution 4 for Transform Input to Output, proposed by محمد حلمي: 
=LET(i,A2:A16,u,B2:B16,e,SCAN(,(u>"")*(i<>A2),
LAMBDA(a,d,IF(d,a,a+1))),r,REDUCE(TOROW(UNIQUE(i)),
UNIQUE(e),LAMBDA(a,d,LET(v,FILTER(u,e=d),IFNA(VSTACK(a,
HSTACK(IF(ROWS(v)=1,v,TAKE(a,-1,1)),TOROW(v))),"")))),
FILTER(r,INDEX(r,,3)>""))
Excel solution 5 for Transform Input to Output, proposed by محمد حلمي: 
=LET(
a,A2:A16,
b,B2:B16,
n,--b,
v,LAMBDA(x,SCAN(,IFERROR(x,),LAMBDA(a,d,
IF(d,1+a,a)))),
VSTACK(TOROW(UNIQUE(a)),HSTACK(A2&
TOCOL(IF(n,v(FIND(A2,a))),2),
IFNA(DROP(REDUCE(0,SEQUENCE(COUNT(b)),
LAMBDA(a,d,
VSTACK(a,TOROW(FILTER(b,v(n)=d))))),1,-1),""))))
Excel solution 6 for Transform Input to Output, proposed by Duy Tùng: 
=LET(a,A2:A16,b,B2:B16,c,ROW(b),f,LAMBDA(v,FILTER(v,LEN(b)=1)),h,LAMBDA(s,XLOOKUP(c,c/s,b,,-1)),u,PIVOTBY(HSTACK(f(h(a=A2)),f(h(b<""))),f(a),f(b),CONCAT,,0,,0),IF(TAKE(u,1)&TAKE(u,,1)="",{"Hall","Date"},u))

&&&

Leave a Reply