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 contai...
  • 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

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft 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
      Icon for Microsoft Employee rankMicrosoft 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        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