Unpivot

[Anonymous]

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? 

[VasTg]

@Anonymous 

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?

[Anonymous]

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. 

[VasTg · Accepted solution]

@Anonymous 

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

Output

If this helps, mark it as solution.

Kudos are good too.

[Anonymous]

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]

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

[Anonymous]

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.