Forum Discussion
Aggregation after join
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!
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.
5 Replies
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.
