Forum Discussion
Task with multiple resources showing as multiple rows in Power BI table; instead of just the one row
Question please.
I have Project on the Web schedule with many tasks, and some have multiple resources assigned. When I pull the tasks into a table in Power BI report I am getting a row for each task with the resource, instead of the task listing both resources on one row. So if there are 4 resources, the task shows 4 times, once per resource.
The tables tasks and resources have a relationship, so is this the way it is supposed to work? How can I just show a list of unique tasks and all the resources assigned in one row per task? Grouping of some sort?
So 1 task with 4 resources, shows as 1 task with 4 resources all in one row.
Thank you
SClark513This can be done in Power Query. However, depending upon the size of your dataset/tables performance may be a concern. How you include this in you model will vary, but the following will show you how to do it then you can determine what works in your model. Anyways, here is what you need to do.
.
Sorry for the formatting, but the new site would no allow me to post this with a number and/or bulleted list formatting.
Create a TaskRsrcList query using msdyn_resourceassignments, including only the msdyn_taskid and msdyn_bookableresourceid fields
Create RsrcLkpList query using the bookableresources, inlcuding only the msdyn_bookableresourceid and Resource name fields
Merge the TaskRsrcList and RsrcLkp tables on msdyn_bookableresourceid column
Go to Transform then click Group By.
In the Group By dialog box:
Select Group by msdyn_taskid
New column name: ResourceList
Operation: All Rows
Click OK.
Now, you will have a table grouped by ID with a nested table in the ResourceList column.
Next, you need to concatenate the resource values:
Add a Custom Column by going to the Add Column tab and clicking on Custom Column.
Name new column Concatenated Resources
In the Custom Column dialog box, enter the following formula:Text.Combine([ResourceList][Resource], ", ")Click OK.
Remove the ResourceList column if you no longer need it
Incorporate this into your model as needed.
3 Replies
SClark513This can be done in Power Query. However, depending upon the size of your dataset/tables performance may be a concern. How you include this in you model will vary, but the following will show you how to do it then you can determine what works in your model. Anyways, here is what you need to do.
.
Sorry for the formatting, but the new site would no allow me to post this with a number and/or bulleted list formatting.
Create a TaskRsrcList query using msdyn_resourceassignments, including only the msdyn_taskid and msdyn_bookableresourceid fields
Create RsrcLkpList query using the bookableresources, inlcuding only the msdyn_bookableresourceid and Resource name fields
Merge the TaskRsrcList and RsrcLkp tables on msdyn_bookableresourceid column
Go to Transform then click Group By.
In the Group By dialog box:
Select Group by msdyn_taskid
New column name: ResourceList
Operation: All Rows
Click OK.
Now, you will have a table grouped by ID with a nested table in the ResourceList column.
Next, you need to concatenate the resource values:
Add a Custom Column by going to the Add Column tab and clicking on Custom Column.
Name new column Concatenated Resources
In the Custom Column dialog box, enter the following formula:Text.Combine([ResourceList][Resource], ", ")Click OK.
Remove the ResourceList column if you no longer need it
Incorporate this into your model as needed.Thank you - this worked perfectly. I just had to rename my resource 'name' field as it was called 'name' for the concat part =
Table.AddColumn(#"Grouped Rows", "Concatenated Resources", each Text.Combine([ResourceList][name], ", "))
Appreciate you - thanks so much!
Hello SClark513 ,
This will be down to the data model that you have created in Power BI. If you want to show resources on the tasks without duplicating the task rows, you'll need to create a CSV list of resources per task ID then use that field on the visual so that it doesn't duplicate the task row per resource. There are probably other ways to do it to.
Paul
