Great participation on this workout! Well done
Interestingly to learn more I type in some solutions to chatGPT and got amazing outputs explaining what each step of the code is doing. This is amazing for learning.
The provided M code performs a series of transformations on a table named “Data” to reshape and aggregate the data. Here’s a breakdown of what each step in the code is doing:
-
Source = Table.RemoveColumns(Excel.CurrentWorkbook(){[Name="Data"]}[Content], "Customer Nr."):- This step removes the column “Customer Nr.” from the table named “Data” in the current Excel workbook.
- The resulting table is assigned to the variable named “Source”.
-
UnpivotOtherCols = Table.UnpivotOtherColumns(Source, {"Location", "Customer"}, "cw", "Value"):- This step unpivots all columns in the “Source” table except for the “Location” and “Customer” columns.
- The “Location” and “Customer” columns are the key columns, and the resulting column names are stored in the “cw” column.
- The unpivoted values are stored in the “Value” column.
- The resulting table is assigned to the variable named “UnpivotOtherCols”.
-
Group = Table.Group(UnpivotOtherCols, {"cw", "Location"}, {{"All", each _, type table [Location=text, Customer=text, Attribute=text, Value=number]}, {"MaxGroup", each List.Max([Value]), type number}}):- This step groups the “UnpivotOtherCols” table by the columns “cw” and “Location”.
- It creates two new columns in the resulting table:
- “All”: It contains the entire group as a table.
- “MaxGroup”: It calculates the maximum value of the “Value” column within each group.
- The resulting table is assigned to the variable named “Group”.
-
Expand = Table.RemoveColumns(Table.SelectRows(Table.ExpandTableColumn(Group, "All", {"Customer", "Value"}, {"Customer", "Value"}), each [Value] = [MaxGroup]), {"Value", "MaxGroup"}):- This step expands the “All” column in the “Group” table to include the “Customer” and “Value” columns.
- It selects only the rows where the “Value” column is equal to the “MaxGroup” column.
- Finally, it removes the “Value” and “MaxGroup” columns from the resulting table.
- The resulting table is assigned to the variable named “Expand”.
-
PivotLocation = Table.Pivot(Expand, List.Distinct(Expand[Location]), "Location", "Customer"):- This step pivots the “Expand” table based on the unique values in the “Location” column.
- The values from the “Customer” column are spread across the pivoted columns.
- The resulting table is assigned to the variable named “PivotLocation”.
The final result of the code is the table represented by “PivotLocation”. It represents the reshaped and aggregated data based on the provided transformations.
Note that the code assumes the existence of a table named “Data” in the current Excel workbook.
