Please find attached the SQL and PowerQuery function for your reference. Also, I have applied the Power Query function to your sample Excel file too.
Here’s a breakdown of how the UnpivotStores Power Query function works, step by step:
Function Purpose
The function takes a wide-format table (with many store columns and their corresponding T&F columns) and transforms it into a long-format table with three dynamic columns:
Store NameAssortment (value from the store column)TF (value from the corresponding T&F column)
It also retains the first 12 static columns and filters out rows where both Assortment and TF are '0', 'NO', or null.
Step-by-Step Explanation
1. Function Definition
UnpivotStores = (rawTable as table) =>
Defines a function named UnpivotStores that takes a table (rawTable) as input.
2. Static Columns
staticCols = {
"CAT", "Ctegory", "Group Name", "Sub-Family", "Brand-Name", "Barcode", "Article", "Model No",
"POWER SKU", "MSL QTY", "Reg-SP", "SP+VAT"
}
These are the columns that remain unchanged in the output.
3. Store–T&F Pairs
storePairs = {
{"Dubai Mall-DM1- 1404", "T&F"},
{"DCC-D01-1402", "T&F2"},
...
}
Each pair represents a store column and its corresponding T&F column.
4. Transform Each Pair
transformedTables = List.Transform(storePairs, each
let
storeCol = _{0},
tfCol = _{1},
selectedCols = Table.SelectColumns(rawTable, staticCols & {storeCol, tfCol}),
renamed = Table.RenameColumns(selectedCols, {{storeCol, "Assortment"}, {tfCol, "TF"}}),
addedStore = Table.AddColumn(renamed, "Store Name", each storeCol),
filtered = Table.SelectRows(addedStore, each not (
List.Contains({"0", "NO", null}, [Assortment]) and
List.Contains({"0", "NO", null}, [TF])
))
in
filtered
)
This block does the heavy lifting:
- Selects the static columns + current store and T&F columns.
- Renames the store column to
Assortment and T&F column to TF.
- Adds a new column
Store Name with the store name as a string.
- Filters out rows where both
Assortment and TF are '0', 'NO', or null.
5. Combine All Transformed Tables
combined = Table.Combine(transformedTables)
Merges all the filtered tables into one long-format table.
6. Return the Result
in combined
Returns the final unpivoted and filtered table.
Benefits of This Approach
- Reusable: You can apply it to any similar dataset by calling
UnpivotStores(raw_data).
- Clean: Keeps logic modular and readable.
- Flexible: Easy to update store–T&F pairs or static columns.
Hope this helps
unpivot_sql_query.sql (8.5 KB)
Unpivot_pq_query.pq (2.0 KB)
Matrix_test.xlsx (261.6 KB)
