Unstacking Forecast and Actual Data from a Single Column

[meikastler]

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. 

[ImkeF]

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 formula

Date.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.

[meikastler]

This was GOLD! Thank you so, so much! 😍

[ImkeF · Accepted solution]

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"

[meikastler]

IT WORKED!!! OMG THANK YOU!!!

As a last question, how do I get the pivoted months to be in order?

[ImkeF]

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".

[meikastler]

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 😣

[ImkeF]

Hello @meikastler ,
What about the jan amounts in row 1 and 11: Should they be aggregated to 700 or kept separately?

[meikastler]

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.