Excel power Query: Grouping and transposing

Question

I have this table in SQL which I will constantly have new storedId and newproductID when I will refresh my data table.

StoreID   ProductID  Quantity
1           A          4
1           B          5
1           B          9
2           B          3
2           C          4       
.
.                    
7           F          2

I want to group my StoreID together and transpose the Product ID with the sums of Quantity like the table bellow. Each of my store can have between 0 and 30 products. So If i have 30 products, I would like to have 30 columns "productID" and 30 columns "SumQuantity"

StoredId   ProductId1   ProductID2  ...   SumQuantity1   SumQuantity2 ...
1              A            B                4               14
2              B            C                3                4
.
.
7              F                             2
.
.

How i can do this in Power Query Excel?

horseyride · Accepted Answer · 2021-02-19 15:34:52Z

Its hard to do this without custom coding.

Basically ..

Group on StoreID and ProductID then combine the Quantity

Group again on StoreID, and then combine rows for ProductID and Quantity with semicolon delimiter

Split out the columns on the delimiter. This requires 4 lines of custom code, since we don't know how many columns we need in advance.

Convert the numerical ones to numbers from text, which was needed format for above steps

Sample:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYhOlWB0IzwmITVF4lnCeMxAbGsG5LiCuMZhrBFWL4DnDTTUHstyAGKgxFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StoreID = _t, ProductID = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Quantity", type number}}),
 // group and sum quantities
#"Grouped Rows" = Table.Group(#"Changed Type", {"StoreID", "ProductID"}, {{"Quantity", each List.Sum([Quantity]), type number}}),
// group second time and combine ProductID and Quantity from different rows onto a single row with ; as delimiter
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"StoreID"}, {{"ProductID", each Text.Combine(List.Transform([ProductID], each Text.From(_)), ";"), type text},{"Quantity", each Text.Combine(List.Transform([Quantity], each Text.From(_)), ";"), type text}}),
//Dynamically split the two delimited columns into any number of columns 
DynamicColumnList  = List.Transform({1 ..List.Max(Table.AddColumn(#"Grouped Rows1","Custom", each List.Count(Text.PositionOfAny([ProductID],{";"},Occurrence.All)))[Custom])+1}, each "ProductID." & Text.From(_)),
DynamicColumnList2 = List.Transform({1 ..List.Max(Table.AddColumn(#"Grouped Rows1","Custom", each List.Count(Text.PositionOfAny([Quantity],{";"},Occurrence.All)))[Custom])+1}, each "Quantity." & Text.From(_)),
#"Split Column by Delimiter" =  Table.SplitColumn( #"Grouped Rows1", "ProductID",  Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), DynamicColumnList),
#"Split Column by Delimiter2" = Table.SplitColumn( #"Split Column by Delimiter" , "Quantity", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), DynamicColumnList2),
// convert numerical to numbers   
#"ConvertToNumbers" = Table.TransformColumnTypes (#"Split Column by Delimiter2", List.Transform ( List.Difference(Table.ColumnNames(#"Split Column by Delimiter2"),Table.ColumnNames(#"Split Column by Delimiter")),each {_,type number}))
in #"ConvertToNumbers"

Alexis Olson · Accepted Answer · 2020-11-11 13:51:30Z

1

I'm not sure why you'd want data in such a terrible format but here's how I'd approach it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYhOlWB0IzwmITVF4lnCeMxAbGsG5LiCuMZhrBFWL4DnDTTUHstyAGKgxFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StoreID = _t, ProductID = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StoreID", Int64.Type}, {"ProductID", type text}, {"Quantity", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"StoreID", "ProductID"}, {{"SumQuantity", each List.Sum([Quantity]), type nullable number}}),
    #"Custom Grouping" = Table.Group(#"Grouped Rows", {"StoreID"},
        {
            {"Products", each Table.FromRows({[ProductID]},List.Transform({1..List.Count([ProductID])}, each "ProductID" & Number.ToText(_))), type table},
            {"Quantities", each Table.FromRows({[SumQuantity]},List.Transform({1..List.Count([SumQuantity])}, each "SumQuantity" & Number.ToText(_))), type table},
            {"Count", Table.RowCount, Int64.Type}
        }
    ),
    ColumnsToExpand = List.Max(#"Custom Grouping"[Count]),
    ProductColumns = List.Transform({1..ColumnsToExpand}, each "ProductID" & Number.ToText(_)),
    QuantityColumns = List.Transform({1..ColumnsToExpand}, each "SumQuantity" & Number.ToText(_)),
    #"Expanded Products" = Table.ExpandTableColumn(#"Custom Grouping", "Products", ProductColumns, ProductColumns),
    #"Expanded Quantities" = Table.ExpandTableColumn(#"Expanded Products", "Quantities", QuantityColumns, QuantityColumns)
in
    #"Expanded Quantities"

The first grouping on StoreID and ProductID is the same as @horseyride suggests but I go a different way from there.

The next step is to group by only StoreID and construct the desired table for each store. This step result looks something like this in the editor (the preview on the bottom is what the selected cell contains):

Let's look at how this table is constructed for "Products".

Table.FromRows(
    {[ProductID]},
    List.Transform(
        {1..List.Count([ProductID])},
        each "ProductID" & Number.ToText(_)
    )
)

This list [ProductID] is the list of IDs associated with the current store. For the cell selected, it's simply {"A","B"}. Since there are two values, the List.Count is 2. So the above simplifies to this:

Table.FromRows(
    {{"A", "B"}},
    List.Transform(
        {1,2},
        each "ProductID" & Number.ToText(_)
    )
)

After the list transform this is simply

Table.FromRows({{"A", "B"}}, {"ProductID1", "ProductID2"})

which looks like the preview in the screenshot above.

The construction for the quantities data is analogous. After that column is defined, all that is left is to expand both these columns:

Edit:

As @horseyrides points out, the expansion needs to be made dynamic, so I've added a column to the custom grouping to determine the number of columns to expand into. This number is the maximum count of products for one store, which is then used to generate a list of column names.

edited Nov 11, 2020 at 13:51

answered Nov 10, 2020 at 21:36

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

7 Comments

horseyride Over a year ago

aren't you hard coding the expansion to two columns each? Thats why I went back and made that part dynamic since question said there could be up to 30 columns

Alexis Olson Over a year ago

@horseyride It's only two expansions, not 30. One for products and one for quantity. Just like you have two DynamicColumnLists and two Splits. The List.Count([ProductID]) would expand to 30 along with {[ProductID]}.

Simon GIS Over a year ago

Thanks all for your help! Yes, agree its terrible data format. But yes sometimes for business purposes its easier for the user in this format.

horseyride Over a year ago

@alexisolson I'm not seeing it. If I add 1/c/12 and 1/d/13 to the input table, then the grouping works fine, but the two expansions only result in 2 sets of two columns, not 2 sets of 4 columns

Alexis Olson Over a year ago

@horseyride Ah, I see what you mean now. The expansion automatically creates the right number of columns but when the data changes those steps would need to be recreated. Thanks for pointing that out. I'll update the code in a bit.

|

Collectives™ on Stack Overflow

Excel power Query: Grouping and transposing

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related