Power Query: Beyond the User Interface: Table.Split and More

February 25, 2020 - by Bill Jelen

While the Power Query interface is very powerful, those who can go beyond the interface have super powers. Frank Tonsen sent in this solution. Let’s pick up at the point where the data looks like this:

Frank then codes a Table.Split, specifying that every 5 records should be a new table.

From there, Table.FromList and more. At that point, you can click on any Table cell and see the data in that table. Here is the second Table cell.

Here is Frank’s code:

let
    Source = Excel.CurrentWorkbook(){[Name="UglyData"]}[Content],
    RemovedTotals = Table.RemoveColumns(Source,{"Column2", "Column3", "Column4", "Column5", "Column6"}),
    TransposedTable = Table.Transpose(RemovedTotals),
    PromotedHeaders = Table.PromoteHeaders(TransposedTable, [PromoteAllScalars=true]),
    SplittedTable = Table.Split(PromotedHeaders,5),
    TableFromList = Table.FromList(SplittedTable, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    AddedColumn1 = Table.AddColumn(TableFromList, "Column2", each Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders([Column1])))),
    AddedColumn2 = Table.AddColumn(AddedColumn1, "Column3", each Table.AddColumn([Column2], "Employee", (x) => Table.ColumnNames([Column2]){1})),
    AddedColumn3 = Table.AddColumn(AddedColumn2, "Column4", each Table.RenameColumns([Column3], {Table.ColumnNames([Column3]){1}, "Total"})),
    Combined = Table.Combine(AddedColumn3[Column4]),
    ReorderedColumns = Table.ReorderColumns(Combined,{"Category Description", "Employee", "Q1", "Q2", "Q3", "Q4", "Total"}),
    ChangedType = Table.TransformColumnTypes(ReorderedColumns,{{"Category Description", type text}, {"Employee", type text}, {"Q1", type number}, {"Q2", type number}, {"Q3", type number}, {"Q4", type number}, {"Total", type number}})
in
    ChangedType

Mahmoud Baniasadi sent in this M-Code. The red parts were coded by hand.

let
    Source = Excel.CurrentWorkbook(){[Name="UglyData"]}[Content],
    TransposedTable = Table.Transpose(Source),
    PromotedHeaders = Table.PromoteHeaders(TransposedTable, [PromoteAllScalars=true]),
    AddedConditionalColumn = Table.AddColumn(PromotedHeaders, "Custom", each if Text.Start([Category Description] ,1) <> "Q" then [Category Description] else null),
    FilledDown = Table.FillDown(AddedConditionalColumn,{"Custom"}),
    GroupedRows = Table.Group(FilledDown, {"Custom"}, {{"tbl", each Table.Skip(_,1)}}),
    FilteredRows = Table.SelectRows(GroupedRows, each ([Custom] <> "Dept. Total")),
    Expandedtbl = Table.ExpandTableColumn(FilteredRows, "tbl", Table.ColumnNames(PromotedHeaders)),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(Expandedtbl, {"Custom", "Category Description"}, "Attribute", "Value"),
    PivotedColumn = Table.Pivot(UnpivotedOtherColumns, List.Distinct(UnpivotedOtherColumns[#"Category Description"]), "Category Description", "Value", List.Sum),
    InsertedSum = Table.AddColumn(PivotedColumn, "Total", each List.Sum({[Q1], [Q2], [Q3], [Q4]}), type number),
    RenamedColumns = Table.RenameColumns(InsertedSum,{{"Custom", "Employee Name"}, {"Attribute", "Category Description"}})
in
    RenamedColumns

Goodly from YouTube also shared a video with a custom M solution to dynamically identify all of the columns that start with "Employee". While this would not work in real life when employees are named Andy, Betty, Charlie, it is a cool video: https://www.youtube.com/watch?v=xamU5QLNiew.

Return to the main page for the Podcast 2316 challenge.

Read the next article in this series: Power Query: The World of Bill Szysz.