Forum Discussion
Unstacking Forecast and Actual Data from a Single Column
I am brand new to Power Query and have been stuck on this problem for the past 5 days. I've tried watching tons of YouTube videos (especially by Goodly), but the solutions provided don't help my specific case. I feel that this is an extremely simple problem to fix, I just can't seem to make it happen.
Attached in the picture, you'll find columns with Forecast and Actual, Months, and Quantities.
I'm trying to convert this into a list that looks like:
Months / Forecast / Actual
- the quantities are below the forecast and actual
How can I make this happen?
I really appreciate ANY help that I can get.
No worries 🙂
OK, so if the values in column2 are unique for each value in column1, then a pivot-operation would do the job:
1) Check Column1
2) go to Transform tab and choose "pivot column"
3) Select Column3 for the "Values Column"
8 Replies
No worries 🙂
OK, so if the values in column2 are unique for each value in column1, then a pivot-operation would do the job:
1) Check Column1
2) go to Transform tab and choose "pivot column"
3) Select Column3 for the "Values Column"IT WORKED!!! OMG THANK YOU!!!
As a last question, how do I get the pivoted months to be in order?
Hello meikastler ,
What about the jan amounts in row 1 and 11: Should they be aggregated to 700 or kept separately?They need to be kept seperately. The first January is for the Forecast of a particular product quantity. The second January is for the Actual product quantity.
Hi meikastler ,
that doesn't look to me like so.
In row 11 we have the 2nd jan and the column left to it shows "Forecast".OMG! I did a Replace Values and the extra Jan is supposed to be November and the extra Feb is supposed to be December. I apologize! I took the photo before I fixed that particular error 😣
Good to see 🙂
The values in column1 are text values, so you need an additional helper column for the sorting.
Unfortunately - at least to my knowledge - there isn't a native function that derives the month number from a text string. So the formula is a bit more complicated.
Add a new column with the following formulaDate.Month(Date.FromText("2000-" & [Column1] & "-01"))You can sort by it in the query editor.
But more importantly: You can use this column as the sort-by-column for your Column1 later in the data model.This was GOLD! Thank you so, so much! 😍
