Forum Discussion
Transpose & Pivot Order Problem
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.
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?
2 Replies
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?
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! 😍
