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!
Solved! Go to 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
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
@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
@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.