Forum Discussion
Replacing Monthly File with next 12 months creates new header names each month
I have a forecast file that I connect to my data model and its very simple:
SKU | Channel | M1 | M2 | M3 |...| M12 |
But I have the actual MM/DD/YYYY as the column headers so each month, M1-M12 changes.
The steps I run in the query just unpivot the months, but every time I replace the file the following month I get the error that it doesn't recognize the header its looking for. I've tried pasting a fake additional header on the first row and then make my first step be "Promote Headers" but that doesn't seem to work.
There has to be something simple here I am missing.
Example:
This Month:
| SKU | Location | 9/1/2023 | 10/1/2023 | 11/1/2023 | 12/1/2023 |
| Product A | East | 3.3009 | 3.2624 | 3.2239 | 3.1854 |
| Product A | West | 0 | 0 | 0 | 0 |
| Product B | East | 0.388889 | 0.388889 | 0.388889 | 0.388889 |
| Product B | West | 0 | 0 | 0 | 0 |
| Product C | East | 0 | 0 | 0 | 0 |
| Product C | West | 1.91201 | 1.87351 | 1.83501 | 1.79651 |
Next Month:
| SKU | Location | 10/1/2023 | 11/1/2023 | 12/1/2023 | 1/1/2024 |
| Product A | East | 3.2624 | 3.2239 | 3.1854 | 3.1469 |
| Product A | West | 0 | 0 | 0 | 0 |
| Product B | East | 0.388889 | 0.388889 | 0.388889 | 0.388889 |
| Product B | West | 0 | 0 | 0 | 0 |
| Product C | East | 0 | 0 | 0 | 0 |
| Product C | West | 1.87351 | 1.83501 | 1.79651 | 1.75801 |
4 Replies
- BA_Pete
Super User
Hi antepenultimate ,
Use Unpivot Other Columns instead of Unpivot Columns:
Multi-select (Ctrl+click) [SKU] and [Location].
Go to the Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns.
Power Query will now ignore the column names of those it is unpivoting and instead only hardcode those you intially selected.
Pete
This sounds pretty good. So the selections are the only column headers that it looks at?
- BA_Pete
Correct. The selected columns are the only ones that get hardcoded, the rest get unpivoted as required.
Pete
Have you put all months into your table in the source data? If the transform file table is different everytime it refreshes this will cause issues with your query as it'll always look for the original format. May be worth taking the months out two or three years on the original table and hopefully it should load correctly moving forward.
