How to Unpivot Monthly Customer and Brand Targets in WPS Office Power Query
Question details
The user needs to reshape a wide monthly customer and brand target file into a normalized long format for data modeling in WPS Office Power Query.
- Product
- WPS Office Spreadsheets - Power Query
- Device & OS
- not provided
- Scenario
- A target workbook has one row per customer and brand and separate columns for each month; the user wants to load monthly targets as a fact table connected to customer, brand, and date dimensions.
- Observed behavior
- The source data is in wide format, so the monthly target columns must be unpivoted into Month and Target columns before building relationships.
Open the target workbook in WPS Office Spreadsheets and confirm that Customer Name, Brand, Total Target, and the monthly columns have consistent header names. Make sure the source range has no fully merged cells or blank rows before you open Power Query.
Unpivot Monthly Target Columns in Power Query Editor
Use this method when you have one row per customer and brand with separate month columns and need to create a normalized target fact table.
In a wide target table, each month is stored as a separate column. Unpivoting keeps Customer Name, Brand, and Total Target as identifying columns and turns all monthly columns into two columns: Month and Target. This normalized shape is easier to relate to a date dimension and simplifies refreshes when new months are added.
In WPS Office Spreadsheets, click any cell inside the target range, go to Data > Get Data > From Table/Range, and confirm the range includes all customer, brand, total target, and monthly columns.
In the Power Query Editor, select Customer Name, Brand, and Total Target columns, right-click the selection, and choose Unpivot Other Columns to convert the remaining month columns into Attribute and Value columns.
Right-click the Attribute column header, choose Rename, and type Month. Right-click the Value column header, choose Rename, and type Target.
Go to Home > Close & Load > Close & Load To, select Only Create Connection or a table destination, and enable Add this data to the Data Model if you are building relationships in the same workbook.
In the data model or Power Pivot window, create relationships from the target fact table’s Customer, Brand, and Month columns to the corresponding customer, brand, and date dimension tables.
Normalize Monthly Customer and Brand Targets with WPS Office Power Query
Use WPS Office Spreadsheets to unpivot monthly customer and brand target columns into a long Month and Target table, then load it into your data model and relate it to the Customer, Brand, and Date dimensions.
- 1. Open the target workbook: Open WPS Office Spreadsheets and load the wide customer and brand target file.
- 2. Start Power Query: Select a cell in the range, then go to Data > Get Data > From Table/Range.
- 3. Unpivot monthly columns: Select Customer Name, Brand, and Total Target, right-click, and choose Unpivot Other Columns.
- 4. Rename and load: Rename Attribute to Month and Value to Target, then use Close & Load to create the normalized table.
- 5. Create relationships: Open the data model and connect Month to the date dimension, plus Customer and Brand to their related dimension tables.
Frequently Asked Questions
Why should monthly target columns be unpivoted instead of kept as separate month columns?
Unpivoting creates a single Month column and a single Target column, so the model can use one relationship to a date dimension instead of managing many separate month columns. This normalized long format is usually easier to refresh and analyze.
How do I keep Customer Name and Brand when unpivoting in WPS Power Query?
Select Customer Name, Brand, and Total Target first, then use Unpivot Other Columns. The selected columns remain identifying columns and are repeated for each unpivoted month row.
Can I load the unpivoted target table directly into the data model?
Yes. In the WPS Office Spreadsheets Power Query Editor, use Close & Load To and choose the connection or table destination that adds the result to the Data Model. Then create relationships from the target table to dimension tables.
What if my monthly columns are named Jan, Feb instead of full month names?
After renaming the Attribute column to Month, you can use Transform options or replace values to convert abbreviations such as Jan and Feb into the same date keys used by your date dimension.
