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

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