Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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:
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.
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:
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.
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.
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |