Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Try a custom column in Project like:
Resources = CONCATENATEX(VALUES(Resource[ResourceName]),[ResourceName],",")
I assume that Project and Resouce are related on ProjectName.
@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],",")
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
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...
@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
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
@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
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
@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
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
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
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
Thanks! That fixed it all!
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |