Unpivot columns and convert to date?

[Anonymous]

So I don't see anywhere that I can attach a file so I'll have to use excel screenshots to show my example. The first screenshot below is an example excel workbook of how the data will be brought into my Power BI model. The screenshot after it is how I need the data trasformed. I know I can select all the Actual and Budget columns and then do "Unpivot Columns." I guess I am stuck on how to then create a measure to differentiate between actuals and budget amounts while also converting the "Jan 17" "Feb 17" etc to a date type field. Is it possible to take this original data source and then have measures based on "Actual" or "Budget" based on date?

Thanks for the help!

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

After test, the solution @RolandsP posted is right. After unpivot all columns, you will get the following data.



Then select the [Attribute] click Split column by delimiter (Select "space" and right-most delimiter) as follows, you will get expected result.



Best Regards,
Angelia

[RolandsP]

You have to do following:

1) Select and unpivot all Value columns.

2) Select column that contains date and "actual" or "budget" attribute. Then do Split column by delimiter (Select "space" and right-most delimiter).

3) Then you should have 3 columns: Date, Budget/Actual and Value.