Transpose & Pivot Order Problem

[meikastler]

Hello wonderful Power BI humans! 

I'm only 1 month into learning Power Query and I am STUMPED by this problem that my boss gave me. I'm trying to find a way to - - Connect the data from this spreadsheet to the end table I want to make, using power query, and then use the end table to create a dashboard. (Attachment incoming)

BUT

I am struggling to pivot / transpose the table correctly. Last week I learned about a new function to help pivot the columns I wanted into headers, but that was for a single SKU. I have something with 111 SKUs. I thought that the skill would transfer over, but I got a bunch of errors. 

I have attached a fake roadmap spreadsheet and changed all of the information. I need help learning what steps to take to unpivot this to get only the OEM / Make / Model / SKU / Month / 2022 Forecast / 2022 Actual / 2023 Forecast / 2023 Actual. 

I'm able to get it down to a certain point (screenshot attached), but from there I am stumped. 

Can anyone help me 😥 

Thank you SO much for taking the time to read this and I am very grateful to ANYONE that can get me a least a little bit further into solving this. 

Google Drive Link  This is an example for only two SKUs, but there are almost 111 in total.

[AlienSx · Accepted solution]

Hello, @meikastler try the following steps (from the table on the screenshot)

 - promote headers

 - unpivot all columns except first 2 (Type and Month)

 - pivot Type column

Then you'll need to split prod description column (created by unpivot step) to get OEM/Make/Model/SKU or whatever in separate columns as well as reorder forecast/actual columns either manually or M code. Is this the way you are looking for?

[meikastler]

AlienSx, THANK YOU!

I knew there had to be an easy way to do this and I was just too inexperienced to find the right combo in my early stages. This was an AMAZING learning experience and now I can do it to the bigger table!!

Once again, thank you so, so much! 😍