Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm currently putting together a report regarding the allocation of my workforce in which I'm trying to aggregate values for indirect allocations for a resource. The issue at hand is that I can only use a table that aggregates resource values per project or product team (See below). I have attempted to merge the queries using two available keys (that are crucial for the aggregation to work) the Resource Key and the Period Key. The merge works fine it is when I attempt to use the "Expand - Aggregate" functionality that things go wrong. Since the table (Team table below) I'm trying to aggregate values from contains 950,000 rows the aggregation does not complete (Tried running it for 2 hrs yesterday without completion).
To be clear there is no direct relationship between these two tables as it would be a many-to-many relationship.
Is the merge + expand - aggregate my only option or do you see any alternatives?
In my Resource Table (the table in which I have a summary of the resources' allocation per month accordingly:
Resource Table:
Resource Key | Period Key (Month) | Allocation (HRS) | Indirect Allocation (Aggregated from table below) |
5555555 | 5555555 | 180 | 90 |
1111111 | 1111111 | 90 | 0 |
Since my resources can be allocated to many different parts of my organisation (in projects, product teams etc) I also have a table that aggregate allocations around the teams in which the resources work in:
Resource Key | Period Key (Month) | Team Key | Allocation (HRS) | Activity Type | Indirect Allocation |
5555555 | 5555555 | 1 | 90 | Run | 0 |
5555555 | 5555555 | 2 | 40 | Indir | 40 |
5555555 | 5555555 | 3 | 50 | Indir | 50 |
1111111 | 1111111 | 1 | 90 | Run | 0 |
THANKS!
Solved! Go to Solution.
Hi
coming to think about it, one should probably start with the aggregation and do the merge afterwards:
let
Source = Team,
GroupAndAggregateFirst = Table.Group(Source, {"Resource Key", "Period Key (Month)"}, {{"Sum HRS", each List.Sum([#"Allocation (HRS)"]), type number}, {"Sum Indirect", each List.Sum([Indirect Allocation]), type number}}, GroupKind.Local),
ThenMergeRightOuter = Table.NestedJoin(GroupAndAggregateFirst, {"Resource Key", "Period Key (Month)"}, Resource, {"Resource Key", "Period Key (Month)"}, "Resource", JoinKind.RightOuter),
#"Expanded Resource" = Table.ExpandTableColumn(ThenMergeRightOuter, "Resource", {"Allocation (HRS)"}, {"Allocation (HRS)"})
in
#"Expanded Resource"
This uses the GroupKind.Local-option that returns results much faster. But it requires the groups to exist in sequential order already (like in the example you've given). If thats not the case in your original data, please delete that optional argument.
See also attached file.
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
Hi @SEMattis
please check this article: https://www.thebiccountant.com/2019/10/28/performance-tip-for-aggregations-after-joins-in-power-quer...
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
Hej @ImkeF ,
Thank you for the suggested solution. I tried the solution this morning but it is more or less just as slow as the aggregation I'm afraid.
Do you think it has to do with the fact that I am using two keys (Resource Key & Period Key) when doing the merge?
//SEMattis
Hi
coming to think about it, one should probably start with the aggregation and do the merge afterwards:
let
Source = Team,
GroupAndAggregateFirst = Table.Group(Source, {"Resource Key", "Period Key (Month)"}, {{"Sum HRS", each List.Sum([#"Allocation (HRS)"]), type number}, {"Sum Indirect", each List.Sum([Indirect Allocation]), type number}}, GroupKind.Local),
ThenMergeRightOuter = Table.NestedJoin(GroupAndAggregateFirst, {"Resource Key", "Period Key (Month)"}, Resource, {"Resource Key", "Period Key (Month)"}, "Resource", JoinKind.RightOuter),
#"Expanded Resource" = Table.ExpandTableColumn(ThenMergeRightOuter, "Resource", {"Allocation (HRS)"}, {"Allocation (HRS)"})
in
#"Expanded Resource"
This uses the GroupKind.Local-option that returns results much faster. But it requires the groups to exist in sequential order already (like in the example you've given). If thats not the case in your original data, please delete that optional argument.
See also attached file.
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
Hej again @ImkeF
I got your solution to work which is great. However, what if I want the aggregation to be made in the "Resource table" using the two keys and then to expand the aggregated values in this table? How do I do that?
When I am using the "Expand column - Aggregate" Power BI automatically select the Table.AggregateColumn function but is there anyway of using another function that is a bit smarter than this function. Sorry for all the questions but I'm kind of new to these complex aggregations using two keys.
Thanks for all help so far!
Hi @SEMattis
the fastest way I know with regards to aggregations after merges is in my blogpost that I've linked.
The alternative I've given gives you the opportunity to expand all fields from the Resource table. So just disable load of the current Resource table and use that new table instead.
This is the fastest way you can achieve, as you're circumventing the expand after aggregation completely.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
139 | |
104 | |
104 | |
80 | |
66 |