Forum Discussion
Help with a table visualization
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
Using this M-code would aggregate the resource-table so that it can be combined 1:1 with the projects (either in the Query editor or in Table view):
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([ResourceName][ResourceName], ", ")),
Cleanup = Table.RemoveColumns(TransformColumnToText,{"ResourceName"})
in
Cleanup
There's some magic included in the GroupRows-step, as it not only aggregates (sums) the actual work, but also returns all records of the filtered/grouped projects. One of it's columns ([ResourceName]) will be adressed in the following step and transformed into a comma-concatenated text-field, using "Text.Combine".
ImkeF Your solution worked thanks a lot!!!!
Greg_Deckler for some reason your solution partially worked. A new column was added but in each row it had all the resource names and not just those that worked in that specific project.
Can anyone help me making some relationships between the two tables? I tried but for some reason I always get an error even when I trie to relate the project name.
the columns of each table are as follows:
Projects table: ProjectName, ProjectDepartment, ActualWorkfromTimesheets(in hours), ActualWorkfromProjectonline(in hours), ClientName, Mandaysfromtimesheets, ProposedMandays, Completion%, ResourceNames(the column with all the resource names per project that you helped me with)
Resources Table:
ResourceName, Resource Department, ProjectName, Week#, WeekEndDate, WeekStartDate, TimesheetActualWork(in hours), MandaysfromTimesheets, ProjectDepartment, ClientName, ProposedMandays, Completion%
As you can see at this point my table are a bit two complex. for some reason I couldn't build any relationships between them and I used Table.Nestedjoin and copied some columns from the one to another in order to use in visualizations. But this is not ideal as far as I know.
Both tables have almost the same columns but the rows are different, as you can see in my first post on this thread.
Ideally I would like to have a project specific table, with columns such as:
ProjectName, ProjectDepartment, ActualWorkfromProjectonline, ClientName,
ProposedMandays, Completion%, ResourceNames(the column with all the resource names per project that you helped me with)
and also a resource specific table with the hours from the timesheets and columns such as: ResourceName, Resource Department, ProjectName, Week#, WeekEndDate, WeekStartDate, TimesheetActualWork(in hours), MandaysfromTimesheets
And then just use relationships to make all the data usable in visualizations.
I know I'm asking a lot but you've been really helpful so far!
What does the error-message say?
ImkeF If I try to relate The project name in both tables I get this error: "We cannot create a relationship between "Project[projectname] and Timesheets[projectname]. This could be because there is missing intermidiate data to connect the two columns."
Both columns have the same data. the only difference is that in the Timesheets[ProjectName] there are multiple rows of each project names because its row represents one input of hours in the timesheets from one resource for that project.
That's a bit surprising - didn't we just create a table with unique values on the Product Name?
The recommendation to normalize your data (split data tables into multiple tables in order to reduce redundant information) would normally only show it's benefitial effects if you create a 1:n-relationship. So maybe you should re-analyze your table structure.
Question is, if this is really needed here. Any signs of performance problems already? Otherwise I'd go with one big table.