Forum Discussion
Helper VHelp 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
Community ChampionGreg_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?
Community ChampionWell, 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],",")
- Sean
Greg_Deckler Thanks!
One issue I didn't foresee and I wonder if mork encounters it too...
When you create a Table - in the bottom TOTAL row - under the Resources column we just created
it ends up concatenating ALL [ResourceName]s - basically all names for all projects.
In the rows above the TOTAL row with the details for each project everything shows up correctly - only the right names.
Very strange because it is data type TEXT and in the bottom TOTAL row of the table it shows ALL names concatenated
Which technically is not wrong - because that's the totals for all projects - so it lists all resources
but I've never seen anything in the TOTAL row of data type TEXT
And my table contains measures that I want to see the Totals for - so I can't just turn off the totals for the whole table???
- Greg_Deckler
I gotta say, that is pretty weird. My only guess is that what is really going on with the total row is that it is evaluating the column in the proper context of the appropriate total. In other words, it is actually overriding any other context filters to evaluate the custom column in what is essentially an ALL context. I have to admit, unexpected, but pretty clever bit of coding that would actually allow it to "total" text columns. Wicked.
- ImkeF
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 CleanupThere'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".
morkImkeF 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!
- ImkeF
What does the error-message say?