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.