Flexible Result Table Creation

(Excel formulas also welcome) Generate Result table as shown. The query needs to be dynamic so that if more rows or columns are added, it should give correct output. For Excel formulas, dynamism is not a requirement, hence they should take the given range only.

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

Solving the challenge of Flexible Result Table Creation with Power Query

Power Query solution 1 for Flexible Result Table Creation, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Subjects = Table.CombineColumns(
    Source, 
    {"Subject1", "Subject2", "Subject3"}, 
    Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), 
    "Merged"
  ), 
  Marks = Table.CombineColumns(
    Table.TransformColumnTypes(
      Subjects, 
      {{"Marks1", type text}, {"Marks2", type text}, {"Marks3", type text}}, 
      "es-PA"
    ), 
    {"Marks1", "Marks2", "Marks3"}, 
    Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), 
    "Merged.1"
  ), 
  Custom1 = Table.TransformColumns(
    Marks, 
    {
      {"Merged", each List.Select(Text.Split(_, " "), each _ <> "")}, 
      {"Merged.1", each List.Select(Text.Split(_, " "), each _ <> "")}
    }
  ), 
  Custom2 = Table.AddColumn(Custom1, "New", each Table.FromColumns({[Merged], [Merged.1]}))[
    [ID], 
    [New]
  ], 
  Expanded = Table.ExpandTableColumn(Custom2, "New", {"Column1", "Column2"}, {"Column1", "Column2"}), 
  Pivoted = Table.Pivot(Expanded, List.Distinct(Expanded[Column1]), "Column1", "Column2")
in
  Pivoted

Power Query solution 2 for Flexible Result Table Creation, proposed by Luan Rodrigues:

let
  Fonte = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content], 
  Col = Table.UnpivotOtherColumns(Fonte, {"ID"}, "Atributo", "Valor"), 
  Group = Table.Group(
    Col, 
    {"ID"}, 
    {{"Contagem", each _, type table [ID = text, Atributo = text, Valor = any]}}
  ), 
  Lista = Table.AddColumn(Group, "Personalizar", each [Contagem][Valor]), 
  Exp = Table.TransformColumns(
    Lista, 
    {"Personalizar", each Text.Combine(List.Transform(_, Text.From), " "), type text}
  ), 
  Tab = Table.AddColumn(
    Exp, 
    "a", 
    each [
      x = Text.Select([Personalizar], {"a" .. "z", "A" .. "Z", " "}), 
      y = Text.Select([Personalizar], {"0" .. "9", " "}), 
      z = Text.Split(Text.Trim(x), " "), 
      w = Text.Split(Text.Trim(y), " ")
    ][[z], [w]]
  ), 
  ExpList = Table.ExpandRecordColumn(Tab, "a", {"z", "w"}, {"z", "w"}), 
  Table = Table.AddColumn(ExpList, "Result", each Table.FromColumns({[z], [w]}))[[ID], [Result]], 
  Exp2 = Table.ExpandTableColumn(Table, "Result", {"Column1", "Column2"}, {"Column1", "Column2"}), 
  Result = Table.Pivot(Exp2, List.Distinct(Exp2[Column1]), "Column1", "Column2")
in
  Result

Power Query solution 3 for Flexible Result Table Creation, proposed by Eric Laforce:

let
  #"▶ColumnNames" = Table.ColumnNames(Source), 
  #"▶Subj_Columns" = List.Select(#"▶ColumnNames", each Text.StartsWith(_, "Subject")), 
  #"▶️Mark_Columns" = List.Select(#"▶ColumnNames", each Text.StartsWith(_, "Marks")), 
  Source = Excel.CurrentWorkbook(){[Name = "tData11"]}[Content], 
  Add_Merge = Table.AddColumn(
    Source, 
    "Merge", 
    each 
      let
        _Subj  = Record.ToList(Record.SelectFields(_, #"▶Subj_Columns")), 
        _Mark  = Record.ToList(Record.SelectFields(_, #"▶️Mark_Columns")), 
        _Table = Table.FromColumns({_Subj, _Mark}, {"Subject", "Mark"})
      in
        Table.SelectRows(_Table, each ([Subject] <> null))
  ), 
  Expand = Table.ExpandTableColumn(Add_Merge[[ID], [Merge]], "Merge", {"Subject", "Mark"}), 
  ChangeType = Table.TransformColumnTypes(Expand, {{"Mark", Int64.Type}}), 
  Pivot = Table.Pivot(ChangeType, List.Distinct(ChangeType[Subject]), "Subject", "Mark")
in
  Pivot

Power Query solution 4 for Flexible Result Table Creation, proposed by Eric Laforce:

let
  Source = Excel.CurrentWorkbook(){[Name = "tData11"]}[Content], 
  ColumnNames = Table.ColumnNames(Source), 
  SetAllAsText = Table.TransformColumnTypes(
    Source, 
    List.Transform(ColumnNames, each {_, type text})
  ), 
  CombineSubjects = Table.CombineColumns(
    SetAllAsText, 
    List.Select(ColumnNames, each Text.StartsWith(_, "Subject")), 
    Combiner.CombineTextByDelimiter(";", QuoteStyle.None), 
    "Subjects"
  ), 
  CombineMarks = Table.CombineColumns(
    CombineSubjects, 
    List.Select(ColumnNames, each Text.StartsWith(_, "Mark")), 
    Combiner.CombineTextByDelimiter(";", QuoteStyle.None), 
    "Marks"
  ), 
  Add_MergeTable = Table.AddColumn(
    CombineMarks, 
    "Merge", 
    each Table.FromColumns({Text.Split([Subjects], ";"), Text.Split([Marks], ";")})
  ), 
  ExpandList = Table.ExpandListColumn(Add_MergeTable[[ID], [Merge]], "Merge"), 
  ExpandRecord = Table.ExpandRecordColumn(
    ExpandList, 
    "Merge", 
    {"Column1", "Column2"}, 
    {"Subject", "Note"}
  ), 
  FilterEmpty = Table.SelectRows(ExpandRecord, each ([Subject] <> "")), 
  ChangeType = Table.TransformColumnTypes(FilterEmpty, {{"Note", Int64.Type}}), 
  Pivot = Table.Pivot(ChangeType, List.Distinct(ChangeType[Subject]), "Subject", "Note")
in
  Pivot

Power Query solution 5 for Flexible Result Table Creation, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

Part 2:
 #"Removed Other Columns" = Table.SelectColumns(#"Added Index1",{"ID", "Attribute", "Value", "Index"}),
 Custom2 = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index",{"Index"},"Added Index1",JoinKind.LeftOuter),
 #"Expanded Added Index1" = Table.ExpandTableColumn(Custom2, "Added Index1", {"ID", "Attribute", "Value", "Subject/Mark", "Index"}, {"ID.1", "Attribute.1", "Value.1", "Subject/Mark.1", "Index.1"}),
 #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Added Index1",{"ID", "Value", "Value.1", "Index"}),
 #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Value", "Subject"}, {"Value.1", "Mark"}}),
 #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"ID", type text}}),
 #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Index"}),
 #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Subject]), "Subject", "Mark")
in
 #"Pivoted Column"

Power Query solution 6 for Flexible Result Table Creation, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Subject1", type text}, {"Subject2", type text}, {"Subject3", type text}, {"Marks1", Int64.Type}, {"Marks2", Int64.Type}, {"Marks3", Int64.Type}}),
 #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
 #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Subject/Mark", each Text.Contains([Attribute],"Subject")),
 #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"Subject/Mark"] = false)),
 #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
 Mark = Table.SelectColumns(#"Added Index",{"ID", "Attribute", "Value", "Index"}),
 Custom1 = #"Added Custom",
 #"Filtered Rows1" = Table.SelectRows(Custom1, each ([#"Subject/Mark"] = true)),
 #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows1", "Index", 1, 1, Int64.Type),

Power Query solution 7 for Flexible Result Table Creation, proposed by Matthias Friedmann:

let
 Source = Excel.CurrentWorkbook(){[Name = "MultipleSubjects"]}[Content], 
 #"Unpivoted Columns1" = Table.UnpivotOtherColumns(
 Source, 
 {"ID"} & List.Select(Table.ColumnNames(Source), each Text.Contains(_, "Marks")), 
 "Attribute", 
 "Value"
 ), 
 #"Unpivoted Columns2" = Table.UnpivotOtherColumns(
 #"Unpivoted Columns1", 
 {"ID", "Attribute", "Value"}, 
 "Attribute.1", 
 "Value.1"
 ), 
 #"Filtered Rows" = Table.SelectRows(
 #"Unpivoted Columns2", 
 each (Text.End([Attribute], 1) = Text.End([Attribute.1], 1))
 )[[ID], [Value], [Value.1]], 
 #"Pivoted Column" = Table.Pivot(
 #"Filtered Rows", 
 List.Distinct(#"Filtered Rows"[Value]), 
 "Value", 
 "Value.1"
 )
in
 #"Pivoted Column"


Formatted by https://www.powerqueryformatter.com/ as mentioned by Brian Julius - I think it does help to understand the code better, especially in a cramped LinkedIn comment to the Excel BI challenge.

Power Query solution 8 for Flexible Result Table Creation, proposed by Matthias Friedmann:

let
  Source = Excel.CurrentWorkbook(){[Name = "MultipleSubjects"]}[Content], 
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"), 
  #"Split Column by Position" = Table.SplitColumn(
    #"Unpivoted Other Columns", 
    "Attribute", 
    Splitter.SplitTextByPositions({0, 1}, true), 
    {"Attribute.1", "Attribute.2"}
  ), 
  #"Pivoted Column1" = Table.Pivot(
    #"Split Column by Position", 
    List.Distinct(#"Split Column by Position"[Attribute.1]), 
    "Attribute.1", 
    "Value"
  )[[ID], [Subject], [Marks]], 
  #"Pivoted Column2" = Table.Pivot(
    #"Pivoted Column1", 
    List.Distinct(#"Pivoted Column1"[Subject]), 
    "Subject", 
    "Marks"
  )
in
  #"Pivoted Column2"

Power Query solution 9 for Flexible Result Table Creation, proposed by Victor Wang:

let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content], 
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"), 
  #"Grouped Rows" = Table.Group(
    #"Unpivoted Other Columns", 
    {"ID"}, 
    {
      {
        "allrows", 
        each 
          let
            rowcount = Table.RowCount(_)
          in
            Table.PromoteHeaders(
              Table.Transpose(
                Table.FromColumns(List.Transform(Table.Split(_, rowcount / 2), each _[Value]))
              )
            )
      }
    }
  ), 
  #"Expanded allrows" = Table.ExpandTableColumn(
    #"Grouped Rows", 
    "allrows", 
    List.Union(List.Transform(#"Grouped Rows"[allrows], each Table.ColumnNames(_)))
  )
in
  #"Expanded allrows"

Power Query solution 10 for Flexible Result Table Creation, proposed by Venkata Rajesh:

let
  Source = Data, 
  Expand = Table.TransformColumnTypes(
    Table.ExpandTableColumn(
      Table.SelectColumns(
        Table.AddColumn(
          Source, 
          "Subjects", 
          each 
            let
              _List = List.RemoveFirstN(List.Select(Record.ToList(_), each _ <> null), 1), 
              _Sub  = List.Count(_List) / 2
            in
              Record.ToTable(
                Record.FromList(List.RemoveFirstN(_List, _Sub), List.FirstN(_List, _Sub))
              )
        ), 
        {"ID", "Subjects"}
      ), 
      "Subjects", 
      {"Name", "Value"}, 
      {"Name", "Value"}
    ), 
    {{"Value", Int64.Type}}
  ), 
  Result = Table.Pivot(Expand, List.Distinct(Expand[Name]), "Name", "Value", List.Sum)
in
  Result

Power Query solution 11 for Flexible Result Table Creation, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Custom1 = List.Transform(
    Table.ToRows(Source), 
    each Table.FromColumns(List.Split(List.Skip(_), 3))
  ), 
  Custom2 = Table.FromColumns({Custom1, Source[ID]}, {"Column1", "ID"}), 
  #"Expanded Column1" = Table.ExpandTableColumn(
    Custom2, 
    "Column1", 
    {"Column1", "Column2"}, 
    {"Column1.1", "Column2.1"}
  ), 
  #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([Column1.1] <> null)), 
  #"Pivoted Column" = Table.Pivot(
    #"Filtered Rows", 
    List.Distinct(#"Filtered Rows"[Column1.1]), 
    "Column1.1", 
    "Column2.1", 
    List.Sum
  )
in
  #"Pivoted Column"

Power Query solution 12 for Flexible Result Table Creation, proposed by Hristo Tsenov:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Custom = Table.AddColumn(
    Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"), 
    "Custom", 
    each Text.Select([Attribute], {"0" .. "9"})
  ), 
  Group = Table.Group(
    Table.TransformColumnTypes(Custom, {{"Value", type text}}), 
    {"ID", "Custom"}, 
    {{"Value", each Text.Combine([Value], ", "), type text}}
  ), 
  Split = Table.SplitColumn(
    Group, 
    "Value", 
    Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), 
    {"Subject", "Marks"}
  ), 
  Pivot = Table.Pivot(
    Table.TransformColumnTypes(Table.RemoveColumns(Split, {"Custom"}), {{"Marks", Int64.Type}}), 
    List.Distinct(Split[Subject]), 
    "Subject", 
    "Marks"
  )
in
  Pivot

Power Query solution 13 for Flexible Result Table Creation, proposed by Cristian Angyal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content], 
  Subjects_ColumnNames = Table.SelectRows(
    Table.FromList(Table.ColumnNames(Source)), 
    each Text.StartsWith([Column1], "Subject") or [Column1] = "ID"
  )[Column1], 
  Marks_ColumnNames = Table.SelectRows(
    Table.FromList(Table.ColumnNames(Source)), 
    each Text.StartsWith([Column1], "Mark") or [Column1] = "ID"
  )[Column1], 
  Subjects_unpivoted = Table.UnpivotOtherColumns(
    Table.SelectColumns(Source, Subjects_ColumnNames), 
    {"ID"}, 
    "Attribute", 
    "Value"
  ), 
  Replaced_Value = Table.ReplaceValue(
    Subjects_unpivoted, 
    "Subject", 
    "Marks", 
    Replacer.ReplaceText, 
    {"Attribute"}
  ), 
  Marks_unpivoted = Table.UnpivotOtherColumns(
    Table.SelectColumns(Source, Marks_ColumnNames), 
    {"ID"}, 
    "Attribute", 
    "Value"
  ), 
  Merge_Subjects_with_Marks = Table.NestedJoin(
    Replaced_Value, 
    {"ID", "Attribute"}, 
    Marks_unpivoted, 
    {"ID", "Attribute"}, 
    "Marks", 
    JoinKind.LeftOuter
  ), 
  Expanded_Merge = Table.ExpandTableColumn(
    Merge_Subjects_with_Marks, 
    "Marks", 
    {"Value"}, 
    {"Value.1"}
  ), 
  Pivoted_Results = Table.Pivot(
    Table.SelectColumns(Expanded_Merge, {"ID", "Value", "Value.1"}), 
    List.Distinct(Expanded_Merge[Value]), 
    "Value", 
    "Value.1"
  )
in
  Pivoted_Results

Power Query solution 14 for Flexible Result Table Creation, proposed by Alexandru Badiu:

let
 Source = Datasource, 
 Logic = Table.AddColumn(Source, "Custom", 
 each let
 ColSubject = Table.ToList(
 Table.SelectRows(
 Table.FromList(Table.ColumnNames(Source)), 
 each Text.Contains([Column1], "Subject")
 )
 ), 
 ColMarks = Table.ToList(
 Table.SelectRows(
 Table.FromList(Table.ColumnNames(Source)), 
 each Text.Contains([Column1], "Marks")
 )
 ), 
 CreateRecord = _, 
 SelSubject = List.RemoveMatchingItems(
 Table.SelectRows(Record.ToTable(CreateRecord), each List.Contains(ColSubject, [Name]))[
 Value
 ], 
 {""}
 ), 
 SelMarks = List.RemoveMatchingItems(
 Table.SelectRows(Record.ToTable(CreateRecord), each List.Contains(ColMarks, [Name]))[
 Value
 ], 
 {""}
 ), 
 MixLists = List.Zip({SelSubject, SelMarks})
 in
 MixLists
 ),

Power Query solution 15 for Flexible Result Table Creation, proposed by Bohdan Duda, PhD:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivoted = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"), 
  #"Added Column" = Table.AddColumn(
    Unpivoted, 
    "Custom", 
    each if Text.Contains([Attribute], "Marks") then [Value] else null
  ), 
  #"Filtered Rows" = Table.SelectRows(#"Added Column", each ([Custom] <> null)), 
  #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type), 
  Custom1 = Unpivoted, 
  #"Filtered Rows1" = Table.SelectRows(Custom1, each not Text.Contains([Attribute], "Marks")), 
  #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1", {"Attribute"}), 
  #"Added Index1" = Table.AddIndexColumn(#"Removed Columns1", "Index", 1, 1, Int64.Type), 
  #"Merged Queries" = Table.NestedJoin(
    #"Added Index1", 
    {"Index"}, 
    #"Added In&dex", 
    {"Index"}, 
    "Added Index1", 
    JoinKind.LeftOuter
  ), 
  Expanded = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Custom"}, {"Custom"}), 
  #"Removed Columns2" = Table.RemoveColumns(Expanded, {"Index"}), 
  #"rez" = Table.Pivot(
    #"Removed Columns2", 
    List.Distinct(#"Removed Columns2"[Value]), 
    "Value", 
    "Custom"
  )
in
  #"rez"

Power Query solution 16 for Flexible Result Table Creation, proposed by Chandeep Chhabra:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ids = List.Repeat(Source[ID], List.Count(Mark)), 
  Mark = Table.ToColumns(
    Table.SelectColumns(
      Source, 
      List.Select(
        Table.ColumnNames(Source), 
        each Text.Contains(_, "marks", Comparer.OrdinalIgnoreCase)
      )
    )
  ), 
  Subs = Table.ToColumns(
    Table.SelectColumns(
      Source, 
      List.Select(
        Table.ColumnNames(Source), 
        each Text.Contains(_, "subject", Comparer.OrdinalIgnoreCase)
      )
    )
  ), 
  FlatTable = Table.FromColumns(
    {
      List.Accumulate({Ids}, {}, (s, c) => s & c), 
      List.Accumulate(Subs, {}, (s, c) => s & c), 
      List.Accumulate(Mark, {}, (s, c) => s & c)
    }, 
    {"A", "B", "C"}
  ), 
  #"Filtered Rows" = Table.SelectRows(FlatTable, each [B] <> null and [B] <> ""), 
  #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[B]), "B", "C")
in
  #"Pivoted Column"

Power Query solution 17 for Flexible Result Table Creation, proposed by VIKASH MISHRA:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"), 
  #"Split Column by Position" = Table.SplitColumn(
    #"Unpivoted Other Columns", 
    "Attribute", 
    Splitter.SplitTextByPositions({0, 1}, true), 
    {"Attribute.1", "Attribute.2"}
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Split Column by Position", 
    {{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}
  ), 
  #"Pivoted Column" = Table.Pivot(
    #"Changed Type", 
    List.Distinct(#"Changed Type"[Attribute.1]), 
    "Attribute.1", 
    "Value"
  )[[ID], [Subject], [Marks]], 
  #"Pivoted Column1" = Table.Pivot(
    #"Pivoted Column", 
    List.Distinct(#"Pivoted Column"[Subject]), 
    "Subject", 
    "Marks"
  )
in
  #"Pivoted Column1"

Power Query solution 18 for Flexible Result Table Creation, proposed by Solar Zhu:

let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content], 
  #"Removed Other Columns" = Table.SelectColumns(Source, {"ID", "Subject3", "Marks3"}), 
  #"Renamed Columns" = Table.RenameColumns(
    #"Removed Other Columns", 
    {{"Subject3", "Subject"}, {"Marks3", "Marks"}}
  ), 
  #"Appended Query" = Table.Combine({#"Renamed Columns", Data, #"Data (2)"}), 
  #"Replaced Value" = Table.ReplaceValue(
    #"Appended Query", 
    null, 
    "0", 
    Replacer.ReplaceValue, 
    {"Subject"}
  ), 
  #"Pivoted Column" = Table.Pivot(
    #"Replaced Value", 
    List.Distinct(#"Replaced Value"[Subject]), 
    "Subject", 
    "Marks", 
    List.Sum
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column", {"0"}), 
  #"Replaced Value1" = Table.ReplaceValue(
    #"Removed Columns", 
    null, 
    "", 
    Replacer.ReplaceValue, 
    {"ID", "Physics", "Chemistry", "Biology", "Geography", "Maths", "English"}
  )
in
  #"Replaced Value1"

Solving the challenge of Flexible Result Table Creation with Excel

Excel solution 1 for Flexible Result Table Creation, proposed by John V.:

=LET(i,B3:B8,s,TOCOL(C3:E8&i),m,TOCOL(F3:H8),
u,UNIQUE(TOROW(C3:E8,1),1),
VSTACK(HSTACK(B2,u),HSTACK(i,XLOOKUP(u&i,s,m,""))))

Excel solution 2 for Flexible Result Table Creation, proposed by محمد حلمي:

=SUMPRODUCT((

TOCOL($B$2:$D$7,,1)=B$16)*

(INDEX($A$2:$A$7,MOD(SEQUENCE(ROWS($B$2:$B$7)*3)-1,ROWS($B$2:$B$7))+1)=$A17)*

(INDEX($E$1:$G$1,INT((ROW($E$1:$E$18)-1)/ROWS($

B$2:$B$7))+1)=$E$1:$G$1)*
TOCOL($E$2:$G$7,,1))

Excel solution 3 for Flexible Result Table Creation, proposed by محمد حلمي:

=LET(
A,A1:A7,
B,TOROW(UNIQUE(TOCOL(B2:C7,1))),
C,A2:A7,
VSTACK(HSTACK(A,VSTACK(B,
IFERROR(IFERROR(IFERROR(
XLOOKUP(B&C,B2:B7&C,E2:E7),
XLOOKUP(B&C,C2:C7&C,F2:F7)),
XLOOKUP(B&C,D2:D7&C,G2:G7)),"")))))

Excel solution 4 for Flexible Result Table Creation, proposed by 🇰🇷 Taeyong Shin:

=LET(c,TOCOL(C3:E8,1),DROP(PIVOTBY(TOCOL(IFS(C3:E8>0,B3:B8),2),HSTACK(XMATCH(c,c),c),TOCOL(F3:H8,1),SUM,,0,,0),1))

=LET(id,B3:B8,s,C3:E8,h,UNIQUE(TOROW(s,1),1),result,HSTACK(VSTACK(B2,id),VSTACK(h,MAP(h&id,LAMBDA(x,SUM((s&id=x)*F3:H8))))),IF(result>0,result,""))

Excel solution 5 for Flexible Result Table Creation, proposed by 🇰🇷 Taeyong Shin:

=LET(head, UNIQUE(TOROW(C3:E8, 1), 1),
 array, head & B3:B8,
 lookup, TOCOL(IF(C3:E8<>"", C3:E8 & B3:B8, NA()), 2, 1),
 return, TOCOL(F3:H8, 1, 1),
 body, MAKEARRAY(ROWS(array), COLUMNS(array), LAMBDA(r,c,
 XLOOKUP( INDEX(array, r, c), lookup, return, "")
 )),
 HSTACK( VSTACK("ID", B3:B8), VSTACK(head, body) )
)

Excel solution 6 for Flexible Result Table Creation, proposed by Duy Tùng:

=LET(a,F3:H8,f,LAMBDA(v,TOCOL(IFS(a,v),3)),b,f(C3:E8),c,DROP(PIVOTBY(f(B3:B8),HSTACK(XMATCH(b,b),b),f(a),SUM,,0,,0),1),IF(TAKE(c,1)&TAKE(c,,1)="",B2,c))

Excel solution 7 for Flexible Result Table Creation, proposed by Stefan Olsson:

=query({A2:B,E2:E;A2:A,C2:C,F2:F;A2:A,D2:D,G2:G},"select Col1, sum(Col3) where Col2<>'' group by Col1 pivot Col2",0)

Excel solution 8 for Flexible Result Table Creation, proposed by Viswanathan M B:

=LET(IDs, B3:B8,
Body, C3:H8,
Size, COLUMNS(Body)/2,
Subjects, TAKE(Body,,Size),
UID, TOCOL(Subjects&"_"&IDs),
Marks, TOCOL(DROP(Body,,Size)),
Header_Raw, UNIQUE(TOROW(Subjects),TRUE),
Header, FILTER(Header_Raw, Header_Raw<>0),
Pivot, VSTACK(Header, XLOOKUP(Header&"_"&IDs, UID, Marks,0)),
Firstcol, VSTACK(B2, IDs),
Return, HSTACK(Firstcol, Pivot),
Return)

Solving the challenge of Flexible Result Table Creation with Power Query

Solving the challenge of Flexible Result Table Creation with Excel

Leave a Reply Cancel reply