Excel Power Query; Expand table into columns

Question

I have two tables that I'd like to merge, but I can't quite get the merge to work the way I'd like it to. The first is a list of days and some data, the second is a ranking of items sold that day along with the number sold, etc. I'd like to get the top 'x' items added as columns to my daily data. I got the merge to work, and so now I have a column that says 'table.' However if I expand the table, each item turns into its own row. Instead I'd like table item 2 to repeat all of its columns with a postfix.

date	total_sales	items
1/1/21	$50000	table
1/2/21	$40000	table

date	total_sales	items_1	units_1	items_2	units_2
1/1/21	$50000	pens	15	pencils	10
1/2/21	$40000	erasers	35	pens	5

etc.

I can do this with visual basic but I don't think that's the best way to go about it. Thanks for your help! Also, is there a specific term for this operation that I could have searched for?

Alexis Olson Alexis Olson · Accepted Answer · 2021-11-20T00:55:42

Let's suppose your second table looks like this:

First, let's group by date where for each group we sort and index the subtable. (This is the hardest step.)

Once we have the index for each group, expand that column back so we're back to the start except with the new index column.

From here, you can filter the index to get only the top N and then unpivot the [items] and [units] columns.

Merge the [index] and [Attribute]

and then pivot on [Merged]

Ta da! Now your data is shaped so that you can easily merge it with the first table.

Here's the full code for this example. (You can paste this into the Advanced Editor and look through Applied Steps.)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lEqSM0rBlKGpkqxOugSyZk5YDkDqJwRTC61KLE4tQgkZ2yKLgc1EMM8hB5sWqBWGSnFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, items = _t, units = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"items", type text}, {"units", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"date"}, {{"indexed", each Table.AddIndexColumn(Table.Buffer(Table.Sort(_, {{"units", Order.Descending}})), "Index", 1, 1, Int64.Type), type table}}),
    #"Expanded indexed" = Table.ExpandTableColumn(#"Grouped Rows", "indexed", {"items", "units", "Index"}, {"items", "units", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded indexed", each [Index] < 3),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Filtered Rows", {"date", "Index"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Columns1", {{"Index", type text}}, "en-US"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
    #"Pivoted Column1" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column1",{{"items_1", type text}, {"units_1", Int64.Type}, {"items_2", type text}, {"units_2", Int64.Type}})
in
    #"Changed Type1"

The only place I'm doing special M language magic (not just clicking stuff in the GUI) is this step which I created by applying a couple of steps to one of the sub-tables and pasting the GUI-generated code back into the group by step (plus Table.Buffer wrapper to make sure the sorting "sticks").

#"Grouped Rows" =
    Table.Group(
        #"Changed Type",
        {"date"},
        {
            {"indexed", each
                Table.AddIndexColumn(
                    Table.Buffer(
                        Table.Sort(_, {{"units", Order.Descending}})
                    ),
                    "Index", 1, 1, Int64.Type
                ), type table
            }
        }
    ),

Excel Power Query; Expand table into columns

1 Answers