Excel Power Query _count how many products have per size-CodePudding

in excel power query i have product ,size and stock. I would like to count how many products have stock only to in 1 size,only to in 2 sizes, only to in 3 sizes etc.

thanks in anvance

the desired result

datasample

CodePudding user response：

try this after loading data into PowerQuery (data ... from table/range)

Click select Size and Subcategory columns, right click, group by

New Name: Count, Operation: Count rows. OK

Click select Subcategory and Count columns, right click, group by

New Name: Count1, Operation: Count rows. OK

Click select Count column

transform...pivot column

values column: Count1

sample full code

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"size", "sucategoty"}, {{"Count", each Table.RowCount(_), type number}}),
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"sucategoty", "Count"}, {{"Count.1", each Table.RowCount(_), type number}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows1", {{"Count", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows1", {{"Count", type text}}, "en-US")[Count]), "Count", "Count.1", List.Sum)
in  #"Pivoted Column"