Unpivot table

[yj1111]

Hello,

I have a table which shows the financial info as below:

year        Actuals January Actuals Feburary .... Actuals December Forecast January ... Decemeber Budget January ... December 

2023       

and I want to change the table to the following structure:

year & month  Actuals Forecast Budget

2023.1

...

2023.12

What do I need to do in the power query? Unpivot talbe? Could someone help me with this topic?

Thanks in advance, Jie 

[wdx223_Daniel · Accepted solution]

NewStep=Table.Pivot(Table.SplitColumn(Table.UnpivotOtherColumns(YourTableName,{"year"},"x","Value"),"x",each Text.Split(_," "),{"x","Month"}),{"Actual","Forecast","Budget"},"x","Value")

[yj1111]

Thanks, it works!

[123abc]

To achieve the desired structure in Power Query, you can indeed use the "Unpivot Columns" feature. Here's how you can do it step by step:

Assuming you have a table with the structure you described in Power BI, follow these steps:

1. Open Power BI and load your table into Power Query.

2. In Power Query, select your table.

3. Go to the "Transform" tab in the Power Query Editor.

4. Select the columns that you want to unpivot (in your case, it's "Actuals," "Forecast," and "Budget" columns for all months).

5. With the columns selected, go to the "Unpivot Columns" option in the "Transform" tab.

6. This will transform your table into a structure where you have three columns: "Attribute" (containing month names), "Value" (containing the corresponding values for each month), and "year" (containing the year).

7. Rename the "Attribute" column to "Month" and the "Value" column to "Value."

8. You can now create a new calculated column that combines the "year" and "Month" columns to get "year & month." Use the following formula in the formula bar:

Powerquery

[year] & "." & [Month]

1. Now you have your data in the desired structure with columns "year & month," "Actuals," "Forecast," and "Budget."

2. Close and load your data back into Power BI.

Your table should now have the structure you wanted, with the combined "year & month" column and separate columns for "Actuals," "Forecast," and "Budget" for each month.

Please note that you may need to adjust the column names and data types based on your specific dataset. Make sure that the month names in your "Attribute" column are consistent and in the same format for this unpivoting process to work smoothly.

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.

[yj1111]

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

[123abc]

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:

1. Open Power Query Editor as previously described.

2. Select your table.

3. Go to the "Transform" tab and choose "Transpose" from the drop-down menu.

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

5. Rename the "year" column to "Year & Month."

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

7. You'll now have separate "Year" and "Month" columns.

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

9. Finally, you can rename the newly created columns as needed.

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

[yj1111]

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.