MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Power Query: The World of Bill Szysz


February 25, 2020 - by Bill Jelen

After reviewing 29 different entries, I am declaring the winner to be Bill Szysz! Bill is one of those people who are comfortable typing queries without using the Power Query interface. He writes, “This can be done in dozens of ways. I am sending you four.”

You should download Bill’s solutions and step through them one step at a time using the Applied Steps pane.

Bill’s easiest solution is the Transpose method. Anything in red is code that Bill changed or wrote by hand:

let
    Source = Excel.CurrentWorkbook(){[Name="UglyData"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Indeks", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Indeks", each Number.IntegerDivide(_, 5), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Indeks"}, {{"tbl", each Table.Skip(_, 1), type table}, {"Name", each _{0}[Category Description], type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Name] <> "Dept. Total")),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Filtered Rows", "tbl", Table.ColumnNames(#"Promoted Headers")),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}",{"Indeks"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Category Description", "Name"}, "Atrybut", "Wartość"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[#"Category Description"]), "Category Description", "Wartość", List.Sum),
    #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum({[Q1], [Q2], [Q3], [Q4]}), type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Sum",{{"Atrybut", "Category Description"}})
in
    #"Renamed Columns"

For the Combine Method, Bill says it shows off the of the M language. But he would not use this in a professional application. However, with only four lines of M code, it is impressive. Anything in red is written by hand:

let
    Source = Table.PromoteHeaders(Excel.CurrentWorkbook(){[Name="UglyData"]}[Content] , [PromoteAllScalars=true]),
    Lists = Table.FromColumns({List.Transform({0..((List.Count(Table.ColumnNames(Source))-6)/5)-1}, each List.Range(Table.ColumnNames(Source), _*5+6, 5)) }),
    AlmostReady = Table.Combine(Table.AddColumn(Lists, "Columns", each Table.FromColumns({Table.Column(Source, Table.ColumnNames(Source){0})} & {List.Repeat({[Column1]{0}}, Table.RowCount(Source))} & List.Transform(List.Skip([Column1], 1), each Table.Column(Source, _)), {Table.ColumnNames(Source){0}, "Name"} & List.Transform(List.Skip([Column1], 1), each Text.BeforeDelimiter(_, "_")) )   )[Columns]),
    Ready = Table.AddColumn(AlmostReady, "Total", each List.Sum(List.Skip(Record.ToList(_), 2)))
in
    Ready

Bill’s Helper Table method is written partially by hand:

let
    Source = Excel.CurrentWorkbook(){[Name="UglyData"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    HelperTbl = Table.FromColumns({List.Skip(Table.ColumnNames(#"Promoted Headers"), 1), List.Transform({0..List.Count(Table.ColumnNames(#"Promoted Headers"))-2}, each Number.IntegerDivide(_, 5))}),
    #"Grouped Rows" = Table.Group(HelperTbl, {"Column2"}, {{"tbl", each Table.TransformColumnNames(Table.SelectColumns(#"Promoted Headers", _[Column1] & {Table.ColumnNames(#"Promoted Headers"){0}}), each Text.BeforeDelimiter(_, "_")), type table}}),
    Combined = Table.Combine(Table.AddColumn(#"Grouped Rows", "Tables",  (x) => Table.SelectColumns(Table.AddColumn(x[tbl], "Name", each Table.ColumnNames(x[tbl]){0}), List.Skip(Table.ColumnNames(x[tbl]), 1) & {"Name"})  )  [Tables]),
    #"Filtered Rows" = Table.SelectRows(Combined, each ([Name] <> "Dept. Total")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",List.LastN(Table.ColumnNames(#"Filtered Rows"), 2) & List.RemoveLastN(Table.ColumnNames(#"Filtered Rows"), 2))
in
    #"Removed Other Columns"

Bill’s last method uses an fxUnpivot function that he wrote long ago. Download the workbook to check it out.

To read the next article in this series: One Dynamic Array Formula.

Return to the main page for the Podcast 2316 challenge.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.