Forum Discussion

nholmes12's avatar
nholmes12
Frequent Visitor
4 years ago
Solved

Unpivot Data/ merge table

Hi All, 

 

Still quite new to Power BI myself so looking for assistance with the below. I have the below data set in the following (excel) format which shows change in headcount and cost over time:

 

 FunctionSub FunctionQ1 '2022Q2 '2022Q3 '2022Q4 '2022Q1 '2023Q2 '2023Q3 '2023Q4 '2023Q1 '2024Q2 '2024Q3 '2024Q4 '2024
FTETechnologyBAU and Change121118115114165159151135121120120127.0
 TechnologyData engineering                   -                         -                         -                         -  2222222220212120.0
CostTechnologyBAU and Change2,386,1302,365,7372,295,3372,277,1013,562,1773,460,8353,328,5072,947,1432,774,8302,774,8302,774,8302,902,492
 TechnologyData engineering497,738497,738497,738497,738497,738497,738497,738497,738519,379519,379519,379519,379

 

I have unpivoted the dates to give a more suitable format (below):

 

 

However my Cost and FTE is now lumped into one column (Value). I am not sure whether to have two tables (Cost and FTE) and merge the tables once I have unpivoted the dates, or whether I am able to unpivot in a way that can separate the cost from the FTE?

 

Any suggestions would be much appreciated,

 

Thanks

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi again nholmes12 ,

    I was maybe not enough clear in my previous post.
    To pivot your data : simply select your column "Value Type" and click on Pivot column. In the windows which pop-up select Value as Values column and in Advanced Options select "Don't Aggregate". It should work.


    Please accept it as a solution if it solved your issue.
    Best regards

4 Replies

  • nholmes12's avatar
    nholmes12
    Frequent Visitor
    4 years ago

    Thanks for the response. I did try this option and it worked well. My only thought was that it may limit my ability to create charts that can compare cost to FTE change over time as I only have one value field?

     

    When you say pivot the ValueType and value, that doesn't appear to be working for me. I have pivoted both columns by attribute which gives the following:

    Then pivoted the Value by the type which gives:

     

    Any idea what I am doing incorrectly here?

     

    Thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi again nholmes12 ,

      I was maybe not enough clear in my previous post.
      To pivot your data : simply select your column "Value Type" and click on Pivot column. In the windows which pop-up select Value as Values column and in Advanced Options select "Don't Aggregate". It should work.


      Please accept it as a solution if it solved your issue.
      Best regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi nholmes12 

    If you have a column ValueType containing the information if it is a data FTE or Cost, it should answer your need. Am I right ? (For that simply create that column before your unpivoted column step). You will be able to work on one of the value type using filter.

     


    If you absolutely want to have the value in two separated column, you just to pivot the columns Value Type and Value.

    Please accept it as a solution if it solved your issue