Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
nholmes12
Frequent Visitor

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):

 

nholmes12_0-1631184833323.png

 

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

 

 

 

1 ACCEPTED SOLUTION

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.

AntoineTRICHET_0-1631189666802.png


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

View solution in original post

4 REPLIES 4
nholmes12
Frequent Visitor

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:

nholmes12_0-1631188968849.png

Then pivoted the Value by the type which gives:

nholmes12_1-1631189027431.png

 

Any idea what I am doing incorrectly here?

 

Thanks!

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.

AntoineTRICHET_0-1631189666802.png


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

Thanks very much!

AntoineTRICHET
Resolver III
Resolver III

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.

AntoineTRICHET_0-1631187412581.png

 


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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.