cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mork
Helper V
Helper V

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!

1 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

 

 

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

29 REPLIES 29

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 !

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

Sean
Community Champion
Community Champion

@ImkeF Wow! Amazing!

 

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

 

Thank You again!

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/

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

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.

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

@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?

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

@ImkeF Thank you that did the trick!!!

Sean
Community Champion
Community Champion

@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???

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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors