How to Summarize Product Categories In Power Query

Download Excel File : https://docs.google.com/spreadsheets/d/1e7YskJOI1D1F-zbB5OGYVLPqzxx1OJ1b/edit?usp=sharing&ouid=110135186698985964278&rtpof=true&sd=true
1. Source Step

Source = Excel.CurrentWorkbook(){[Name="Product"]}[Content],

Explanation:
This step loads a table named "Product" from the current Excel workbook into Power Query.
Excel.CurrentWorkbook() references all tables in the current Excel file.
[Name="Product"] selects the table named "Product."
[Content] retrieves the actual data from the selected table.

Result:
The "Product" table is loaded into Power Query for further processing.

2. #"Grouped Rows" Step

#"Grouped Rows" = Table.Group(Source, {"Category"}, {{"Product", each Text.Combine(List.Sort(List.Distinct([Product])),", ")}, {"Total Quantity", each List.Sum([Quantity]), type number}}),

Explanation:
Table.Group groups the data by the "Category" column.
Two new columns are created as part of this grouping:
"Product":
Processes the list of products within each category:
List.Distinct([Product]): Removes duplicate product names.
List.Sort(...): Sorts product names alphabetically.
Text.Combine(..., ", "): Combines sorted product names into a single string, separated by commas.
"Total Quantity":
Calculates the total quantity for each category:
List.Sum([Quantity]): Sums up all values in the "Quantity" column for that category.
type number: Ensures that the result is formatted as a number.

Result:
A grouped table is created with one row per category, showing:
A comma-separated list of distinct, alphabetically sorted products.
The total quantity for each category.

3. #"Changed Type" Step

#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Category", type text}, {"Product", type text}}),

Explanation:
Table.TransformColumnTypes changes the data type of specified columns.
In this case:
The "Category" column is set to text (type text).
The "Product" column is also set to text (type text).

Result:
A table with explicitly defined data types for these columns is created.

4. #"Sorted Rows" Step

#"Sorted Rows" = Table.Buffer( Table.Sort(#"Changed Type",{{"Category", Order.Ascending}}) )

Explanation:
Table.Sort(...):
Sorts the rows in ascending order based on the "Category" column.
Sorting improves readability and organizes data logically.
Table.Buffer(...):
This function loads the sorted table into memory to ensure that sorting is preserved and to improve performance in subsequent steps. It is particularly useful when working with datasets where repeated operations might otherwise disrupt the sorting order.
However, for very large datasets, loading the entire table into memory can significantly increase memory usage. In such cases, using Table.Buffer may negatively impact performance. Therefore, it should be used judiciously based on the size of your data.

Result:
The final table is sorted by "Category" in ascending order.
5. Final Output
in
#"Sorted Rows"

Explanation:
This step outputs the final result from the "Sorted Rows" step as the end result of this query.

Result:
The final table contains:
One row per category, grouped and sorted alphabetically by "Category."
A comma-separated list of distinct and alphabetically sorted products for each category.
The total quantity calculated for each category.

Видео How to Summarize Product Categories In Power Query канала Short Excel