Unstack a column

[Anonymous]

I have a data table in Excel that has many columns. 24 of those columns are named "Budget Jan 2017", "Budget Feb 2017", ... and "Actual Jan 2017", "Actual Feb 2017", etc. Each of these columns contain dollar values. Using Power BI, I was able to unpivot my table to get a single column with 24 unique items as shown. Then I was able to create 2 new columns, one of which contained "Budget" and "Actual" and the other contained the month and year information. The dollar values were then all one one columns So far, so good. Now what I would like to do is unstack my data to get two columns labeled "Budget" and "Actual" which each contain the correct dollar values, matched by date (as well as other identifying columns, like line item, etc.).

Is there a way to do this in Power BI?

Bruce

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

Now you have the table as follows. Please follow the steps to get expected result.




Click "New Table" under modeling on home page, you will get two tables.

Actual =
SELECTCOLUMNS (
    FILTER ( Table4, Table4[Source] = "Actual" ),
    "Date", Table4[Date],
    "Actual", Table4[Amount]
)


Budget =
SELECTCOLUMNS (
    FILTER ( Table4, Table4[Source] = "Budget" ),
    "Date", Table4[Date],
    "Budget", Table4[Amount]
)

Actual TableBudget Table

Then create a relationship between 'Actual' and 'Budget' table.



Finally, in create a calculated column to get actual value.

Actual_value = RELATED(Actual[Actual])

You will get expected result as follows.



Best Regards,
Angelia

[v-huizhn-msft]

Hi @Anonymous,

You can create a new table by clicking "New Table" under modeling on home page, type the following formula(union mutiple columns in your scenario), you will get a new table having one column which includes all buget values, you can use similar formula to get Actual table, then use ADDCOLUMNS to get one merge table.

Budget =
UNION (
    SELECTCOLUMNS ( Table, "Budget", Table[Budget Jan 2017] ),
    SELECTCOLUMNS ( Table, "Budget", Table[Budget Feb 2017] )
)

Actual =
UNION (
 SELECTCOLUMNS ( Table, "Actual", Table[Actual Jan 2017] ),
 SELECTCOLUMNS ( Table, "Actual", Table[Actual Feb 2017] )
)

Then you can create another new table using the similar formula below to get expected result.

Result =
ADDCOLUMNS ( Budget, "Actual", Actual[Actual] )

Best Regards,
Angelia

[Anonymous]

Angelia,

Thank you for the info! I tried your suggestion and I either still do not quite follow it, or I did not explain my original ask properly. I'm going to try again.

Here are two of columns I had in my original data file:

[Original Column]      [Amount]
Budget Jan 2017            $123
Budget Feb 2017           $456
...                                      ...
Budget Dec 2017          $789
Actual Jan 2017            $109
Actual Feb 2017           $500
...                                    ...
Actual Dec 2017          $350

Here is what I got after I split [Original Column]:

[Source]        [Date]          [Amount]
Budget          Jan 2017       $123
Budget          Feb 2017      $456
...                       ...                 ...
Budget          Dec 2017      $789
Actual           Jan 2017       $109
Actual           Feb 2017      $500
...                   ...                    ...
Actual           Dec 2017      $350

What I want is this:

[Date]         [Budget]      [Actual]
Jan 2017       $123           $109
Feb 2017      $456           $500
...                    ...                 ...
Dec 2017      $789          $350

Thank you again!

Bruce

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

Now you have the table as follows. Please follow the steps to get expected result.




Click "New Table" under modeling on home page, you will get two tables.

Actual =
SELECTCOLUMNS (
    FILTER ( Table4, Table4[Source] = "Actual" ),
    "Date", Table4[Date],
    "Actual", Table4[Amount]
)


Budget =
SELECTCOLUMNS (
    FILTER ( Table4, Table4[Source] = "Budget" ),
    "Date", Table4[Date],
    "Budget", Table4[Amount]
)

Actual TableBudget Table

Then create a relationship between 'Actual' and 'Budget' table.



Finally, in create a calculated column to get actual value.

Actual_value = RELATED(Actual[Actual])

You will get expected result as follows.



Best Regards,
Angelia

[Anonymous · Accepted solution]

Thank you, @v-huizhn-msft! That worked! Although I discovered I could not create a relationship between the two tables since I didn't have any column that had unique values. However, by concatenating 3 columns of information, I was able to create a column in each table that had unique values that provided a 1-to-1 match.

Bruce