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
SEMattis
Advocate II
Advocate II

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 KeyPeriod Key (Month)Allocation (HRS)Indirect Allocation (Aggregated from table below)
5555555555555518090
11111111111111900

 

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 KeyPeriod Key (Month)Team KeyAllocation (HRS)Activity TypeIndirect Allocation
55555555555555190Run0
55555555555555240Indir40
55555555555555350Indir50
11111111111111190Run0

 

THANKS!

1 ACCEPTED 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

View solution in original post

5 REPLIES 5
ImkeF
Super User
Super User

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

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.