Help with a table visualization

[mork]

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]

You have to add another "grouping-round" before:

let
    Source = ProjectQueries,
    GroupAgents = Table.Group(Source, {"JP", "AGENT"}, {{"Count", each Table.RowCount(_), type number}}),
    CountAgent = Table.AddColumn(GroupAgents, "Custom", each [AGENT]&" ("&Text.From([Count])&")"),
    RemoveCols = Table.RemoveColumns(CountAgent,{"AGENT", "Count"}),
    GroupRows = Table.Group(RemoveCols, {"JP"}, {{"AGENT", each _, type table}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Sort(List.Distinct([AGENT][Custom])), ", "))

! Watch the changed code in the last line !

[Sean]

@ImkeF Wow! Amazing!

I've been avoiding M far too long - trying to get by with just the UI.

Thank You again!

[ImkeF]

OK, so for my understanding you're having a logical 1:n-structure here which totally makes sense. So your aim to reallocate some columns to where they belong in the query-editor should work - also with the technique I've provided.

If the problem "for some reason I can't relate these two tables with project name" is a technical one, this might be due to the different handling of case sensitivity in the query editor (M) and the table view (DAX). This might actually cause you trouble with the table we've just created!!

If you have Product Names like: "MyProduct1" and "Myproduct1", we'd have created 2 lines in the query editor right now. But the table view would consider them as identical: Therefore seeing 2 rows with the same key in the lookup-table: No connection possible.

If that's the case, you must:

1) Decide if both products above are actually the same in your business logic or not

2) If yes: You can consolidate them by lowercasing or UPPERCASING

3) If no: You have to create a new key: http://www.thebiccountant.com/2015/08/17/create-a-dimension-table-with-power-query-avoid-the-bug/

[ImkeF]

BTW: Could also be caused by blanks or other invisible non-printable characters in your key. Cleaning and trimming will help then. Therefore it's always better to take number-formats for your key columns.

[mork]

@ImkeF I managed to create a connection between the two tables and now my data is more clean and simple. I had a blank row in one of the tables that I hadn't noticed.

A problem that I noticed though is with the code you provided at your first post.

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

It looks like it's working fine but after using it on visualizations I get the names of my resources mulitiple times in a cell.

This is happening because of the format of my table.

ProjectName         ResourceName         TimesheetActualWork         Week

      a                                John                                  8                          1

      a                                John                                  5                          2

      a                                George                             8                          1

      b                                John                                  3                          2

      b                                George                              8                          2

using the code you provided i.e. for project a i get the name John twice. and my final table looks something like this:

ProjectName           SumTimesheetActualWork            Resources

       a                                    21                                    John, John, George

       b                                    11                                    John, George

Is there a way to remove duplicates?

[ImkeF · Accepted solution]

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

[mork]

@ImkeF Thank you that did the trick!!!

[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.