Forum Discussion
Unpivot table
NewStep=Table.Pivot(Table.SplitColumn(Table.UnpivotOtherColumns(YourTableName,{"year"},"x","Value"),"x",each Text.Split(_," "),{"x","Month"}),{"Actual","Forecast","Budget"},"x","Value")
Hello,
thanks for your quick reply. But the problem is, when I unpivot the columns Forecast, Actuals and Budget, I will have the attribute with "Forecast 1...12, Actuals 1...12, Budget 1...12" and I only have one value column with all forecast, actuals and budget value in the value column, but I want to three value columns: Forecast, Actuals and Budget. How can I realize it?
Thanks in advance
I understand your concern. To achieve the desired result with separate columns for "Forecast," "Actuals," and "Budget," you can follow a slightly different approach. Instead of using the Unpivot feature, you can use the "Transpose" function in Power Query. Here's how to do it:
Open Power Query Editor as previously described.
Select your table.
Go to the "Transform" tab and choose "Transpose" from the drop-down menu.
After transposing, your table will have "year" as the header for each column, and the months (January to December) will be listed as rows. You will also have columns for "Actuals," "Forecast," and "Budget."
Rename the "year" column to "Year & Month."
Now, you need to split the "Year & Month" column into two separate columns: "Year" and "Month." Select the "Year & Month" column, go to the "Transform" tab, and choose "Split Column" -> "By Delimiter." Use a period (".") as the delimiter.
You'll now have separate "Year" and "Month" columns.
Your table will still have multiple rows for each "Year & Month" combination, one for "Actuals," one for "Forecast," and one for "Budget." You need to pivot these rows into separate columns. Select the "Attribute" column and go to the "Transform" tab, then choose "Pivot Column." In the Pivot Column dialog, select the "Value" column, and for the "Values Column," choose "Attribute." This will pivot the rows into separate columns for "Actuals," "Forecast," and "Budget."
Finally, you can rename the newly created columns as needed.
Close and load the transformed data into Excel.
Now, your table should be in the format you requested, with separate columns for "Year & Month," "Actuals," "Forecast," and "Budget."
This approach transposes your data first and then pivots it to separate the "Actuals," "Forecast," and "Budget" into their respective columns.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Sorry, maybe I miss some other information in my description, I have some other columns, so I am not able to transpose the whole table. And it is hard to find a unique key, so I do not want to have a second table.