Forum Discussion
Help with a table visualization
Hey everyone,
I have two data tables that I have transformed. One is about projects and has columns like ProjectName, ClientName, Completion%, ProposedEffort, ActualEffort etc.
The other is about my resources and its data comes from the timesheets of the microsoft project online. It has multiple rows for each input a resource has made in a project. And has columns like ProjectName, ResourceName, Week#, TimesheetActualWork etc.
Basically my datasets look as follows.
Project dataset:
ProjectName ActualWork ProposedEffort Completion%
a 21 30 70%
b 11 22 45%
Resource Dataset:
ProjectName ResourceName TimesheetActualWork Week
a John 8 1
a John 5 2
a George 8 1
b John 3 2
b George 8 2
I'm trying to contruct a table visualization for my report as follows:
ProjectName ProposedEffort ActualEffort Completion% Resources
a 30 32 100% John,George
b 15 5 30% Geroge, Jim
c 50 25 40% John, George, Jim
I have the data for all the columns and I'm able to construct it but I'm stuck at the resources column. I have to provide the resource data from the resource data set which has multiple rows for one project for each resource. Is there a way to group the resources that worked in each project and make them show as in the table above?
Thanks in advance!
List.Distinct will do that:
let Source = Ressource, GroupRows = Table.Group(Source, {"ProjectName"}, {{"ResourceName", each _, type table}, {"SumTimesheetActualWork", each List.Sum([#"TimesheetActualWork"]), type number}}), TransformColumnToText = Table.AddColumn(GroupRows, "Custom", each Text.Combine(List.Distinct([ResourceName][ResourceName]), ", ")), Cleanup = Table.RemoveColumns(TransformColumnToText,{"ResourceName"}) in Cleanup
29 Replies
Try a custom column in Project like:
Resources = CONCATENATEX(VALUES(Resource[ResourceName]),[ResourceName],",")
I assume that Project and Resouce are related on ProjectName.
Greg_Deckler Thanks your response although not my question gave me an idea for my files
However is there a way to exclude a certain value from being included in the concatenation?
If instead of a name it says "Deleted" could you exclude "Deleted" from being concatenated?
EDIT: Also is there a way to alphabetize the result?
Well, you can filter out certain values but not aware of a way to alphabetize. You might want to look at doing this in M code as part of your query, a lot more options for processing lists and such:
Resources1 = CONCATENATEX(FILTER(VALUES(Timesheet[ResourceName]),[ResourceName]<>"John"),[ResourceName],",")
