Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Greg_Deckler
Super User
Super User

Try a custom column in Project like:

 

Resources = CONCATENATEX(VALUES(Resource[ResourceName]),[ResourceName],",")

I assume that Project and Resouce are related on ProjectName.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Well, 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],",")

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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

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

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

We constructed a column with the resources that worked in each project.

 

But that is not my problem now.

I just want to have cleaner tables that are more easy to use.

 

Let me explain once more how my data looks.

 

I have two tables. the first is a project table. that has all th information on my projects. stuff like project name, client name, proposed mandays etc.

 

ProjectName       ProposedMandays

a                            11

b                            34

c                            23

 

 

the second table is constructed from the timesheets my resources fill every week. each row in this table is an entry in these timesheets. For reference it looks like this:

 

ProjectName         ResourceName         TimesheetActualWork         Week

      a                                John                                  8                          1

      a                                John                                  5                          2

      a                                George                             8                          1

      b                                John                                  3                          2

      b                                George                             8                          2

 

 

As you can see this table's data is the actual work from the timesheets, per week per project and per resource so I have multiple rows for the same project/resource/week.

For some reason I can't relate these two tables with project name so I'm forced to copy some columns from the project table to the timesheets table and also the oposite. in order to have all the data I need in the table that I'm using.

I'm constructing visualizations from both tables, I dont have just one main table.

The problem with the way my data looks right now is that it's hard to manipulate. I have some columns that are the same on both tables because I can't make a relationship between the two tables.

 

I tried merging both tables into one but that didnt work well and wasn't convinient because the project table is arranged per project and the timesheet table is arranged per prioject, per week and per resource. Combining the two tables made the final table really hard to work with...

Sean
Community Champion
Community Champion

@mork I have something very similar to your model

 

When you go to in the Relationship View window you should have

 

Project Table 1 =>>> * Time Sheet Table

 

And when you click on the line connecting the two tables you should see ProjectName field highlighted

(ProjectName should have unique values in the Project table)

 

If this is your set up then @Greg_Deckler formula works if instead of a Calculated Column you create a new Measure

 

Resource List = CONCATENATEX(VALUES(TimeSheetTable[ResourceName]), TimeSheetTable[ResourceName], ", ")

 

Then create a table and use ProjectName field from your Project table and then the Resource List Measure

 

Depending on how many names you have you may have to turn off the table totals

 

Sean
Community Champion
Community Champion

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,{"Resour​ceName"})
in
    Cleanup

 

@ImkeF Thanks for the code above - I made it work for my model

 

I'm not familiar with M - is there a way to exclude a certain value "Deleted" from being included in the "Custom" column?

 

EDIT: Also could the results in each row be alphabetized?

 

Thanks!

Easiest way would be to add a step before the cleanup that filters out rows containing "Deleted". You can use the UI. Just edit the steps and add a step: Check the Custom-column click on the arrow.

 

I don't understand what you mean with alphabetized: Do you want to convert numbers to text-characters?

 

 

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

Thanks!

 

By alphabetized I mean the names sorted A-Z on each row

 

Right now they are just output in the order they are found in the source

 

John, Victor, Bill => Bill, John, Victor

George, Elizabeth => Elizabeth, George

 

Yes, you just squeeze in a List.Sort between Text.Combine and List.Distinct: Text.Combine(List.Sort(List.Distinct.....)))

 

 

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 Thanks!

 

When I add List.Sort - the names get sorted in reverese Z-A - AND all spaces and commas disapper between the names

 

Bill, Victor, Elizabeth => VictorElizabethBill - so they sort but in reverse Z-A and spaces and commas disapper ???

 

When I remove List.Sort it goes back to => Bill, Victor, Elizabeth

That's probably due to brackets at the wrong place. Could you please share the codeline?

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

 

let
    Source = ProjectQueries,
    GroupRows = Table.Group(Source, {"JP"}, {{"AGENT", each _, type table}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Distinct([AGENT][AGENT]), ", ")),
    #"Duplicated Column" = Table.DuplicateColumn(TransformColumnToText, "ALL Agents Working", "Agents Working"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",(", Deleted"),"",Replacer.ReplaceText,{"Agents Working"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Deleted, ","",Replacer.ReplaceText,{"Agents Working"}),
    Cleanup = Table.RemoveColumns(#"Replaced Value1",{"AGENT"})
in
    Cleanup

With the above code I get

ListSort1.png

 

If you are wondering about the other code - here's a brief explanation of my reasoning

I Duplicated the Column so I can see the Original next to the result when I gett rid off Deleted

Then I have 3 scenario with the Deleted

1 - when Deleted is first => Deleted, Name, Name, Name

2 - when Deleted is somewhere in between => Name, Deleted, Name, Name

3 - when Deleted is last => Name, Name, Name, Deleted

after playing around with the code for a while I settled on what you see above

it seems to work for all cases for getting rid of Deleted and the comma

I tried to do it in one line with the OR operator || but it work

 

Here's the code with List.Insert

let
    Source = ProjectQueries,
    GroupRows = Table.Group(Source, {"JP"}, {{"AGENT", each _, type table}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Sort(List.Distinct([AGENT][AGENT]), ", "))),
    #"Duplicated Column" = Table.DuplicateColumn(TransformColumnToText, "ALL Agents Working", "Agents Working"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",(", Deleted"),"",Replacer.ReplaceText,{"Agents Working"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Deleted, ","",Replacer.ReplaceText,{"Agents Working"}),
    Cleanup = Table.RemoveColumns(#"Replaced Value1",{"AGENT"})
in
    Cleanup

 

And the result

ListSort2.png

This is painfully simple 🙂 : You need to shift one of your closing parenthesis' after the AGENTS:

 

TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Sort(List.Distinct([AGENT][AGENT])), ", ")),

 

So the List.Sort-Command didn't stop soon enough. Strange that it didn't error.

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

Thanks! That fixed it all!

 

 

Sean
Community Champion
Community Champion

Hello @ImkeF

 

I am wondering if something else can be added to the code below...

let
    Source = ProjectQueries,
    GroupRows = Table.Group(Source, {"JP"}, {{"AGENT", each _, type table}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Sort(List.Distinct([AGENT][AGENT])), ", ")),
    #"Duplicated Column" = Table.DuplicateColumn(TransformColumnToText, "ALL Agents Working", "Agents Working"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",(", Deleted"),"",Replacer.ReplaceText,{"Agents Working"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Deleted, ","",Replacer.ReplaceText,{"Agents Working"}),
    Cleanup = Table.RemoveColumns(#"Replaced Value1",{"AGENT"})
in
    Cleanup

Is there any way to add a count of AGENT for each JP

 

So result looks like this

JP - Agents Working

1   - Bill (10), Emma (15),

2   - George (5), Victor (10)

3   - etc...

 

Thanks for your help!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.