In Power Query, you can create a table that contains an aggregate value for each unique value in a column. Power Query groups each unique value, performs an aggregate calculation for each value, and pivots the column into a new table. Using this technique, you could, for example, easily get a count for the number of products in each product category.
To pivot a column
Follow these steps:
In the Query Editor ribbon, click Transform > Pivot Columns.
Choose a column from Values Column. For example, choose Product Number.
Click the small arrow to expand Advanced options and choose an Aggregate Value Function. For example, choose Count (All).
As shown in the instructions given above, you can take a products table containing ProductCategory and ProductNumber columns and use the Pivot Column feature to get the count of all products in each category.