Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Unstack a column

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's avatar
    v-huizhn-msft
    8 years ago

    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's avatar
    Anonymous
    8 years ago

    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

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        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