Solved: Help with a table visualization - Page 3

mork · ‎03-21-2016

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!

ImkeF · ‎03-22-2016

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post