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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bchager6
Super User
Super User

Unpivot

Hello all...

I would like to have Resource #, Resource Name, and Resource Hrs/Week columns instead of how the data is organized in the first table below.

I selected the other columns (Total # of FTE Resources and Total # of Contractor resources) and then unpivoted other columns and arrived at the second table below. Is anyone able to guide me on the next steps? 

Unpivot.JPG

 

Unpivot2.JPG

 

1 ACCEPTED SOLUTION

@bchager6 

 

I am not sure how to create the table for dynamic number of Resource # Name and Hrs/Week columns. Maybe someone out here could help with M query.

 

If we know the columns are fixed, i could use DAX to create a new table and generate some kind of index as key.

 

Dax:

 

Table 2 = UNION(SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 1 Name],"Hrs/Week",'Table (3)'[Resource 1 Hrs/Week]),
SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 2 Name],"Hrs/Week",'Table (3)'[Resource 2 Hrs/Week]),
SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 3 Name],"Hrs/Week",'Table (3)'[Resource 3 Hrs/Week]),
SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 4 Name],"Hrs/Week",'Table (3)'[Resource 4 Hrs/Week]))
 
 
Input
cap12.PNG
 
Output
Cap11.PNG
 
If this helps, mark it as solution.
Kudos are good too.
Connect on LinkedIn

View solution in original post

6 REPLIES 6
VasTg
Memorable Member
Memorable Member

@bchager6 

 

Where is the Resource # column? Are you refering to  # of FTE or # of Contractor columns?

 

Do you only have 4 sets of Resource # Name and Resource Hrs/Week columns?

 

 

 

 

Connect on LinkedIn

That's the issue I'm having with this data. There are no separate Resource # columns. The FTE, Contractor, and all other columns are organized how we would hope they would be. The Resource columns are not.

I want to be able to add a Project name page level filter, for example, and then have a table that lists all of the resources and number of hours they're working on that project instead of having to create a table with the Resource #1 Name, Resource #1 Hrs/Week, Resource #2 Name, Resource #2 Hrs/Week, etc., fields pulled in. 

@bchager6 

 

I am not sure how to create the table for dynamic number of Resource # Name and Hrs/Week columns. Maybe someone out here could help with M query.

 

If we know the columns are fixed, i could use DAX to create a new table and generate some kind of index as key.

 

Dax:

 

Table 2 = UNION(SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 1 Name],"Hrs/Week",'Table (3)'[Resource 1 Hrs/Week]),
SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 2 Name],"Hrs/Week",'Table (3)'[Resource 2 Hrs/Week]),
SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 3 Name],"Hrs/Week",'Table (3)'[Resource 3 Hrs/Week]),
SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 4 Name],"Hrs/Week",'Table (3)'[Resource 4 Hrs/Week]))
 
 
Input
cap12.PNG
 
Output
Cap11.PNG
 
If this helps, mark it as solution.
Kudos are good too.
Connect on LinkedIn

I appreciate your time and effort here. If I understand you correctly; what's critical to this approach is creating a key so it can relate to the original table that contains the project information. That creates a whole other challenge.

 

When all is said and done, the approach may be to ask the data owner to restructure their data. 

amitchandak
Super User
Super User

What is the analysis you want to perform will decide the step?

You have to rename the column. And then use it as per need.

 

Refer

https://radacad.com/pivot-and-unpivot-with-power-bi

I thought I was clear regarding what I'm trying to accomplish, but to add more context, I'd like to be able to pull a "Resource" field in to a table instead of the "Resource #1, Resource #2, etc. fields.

The link you sent just shows how to perform the steps that I already took.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.