# Aggregating across many-to-many relationship in Power Query?

• ### Question

• Hi, another M question.

I am trying to push all my DAX calculated column logic back to Power Query and what I couldn't figure out is how to aggregate across my many to many relationship.

Customer has many companies. He has sales for each company on a bunch of dates. Also, he has timesheets for each company for each date. Customer would like to calculate the labour cost for each company and show them as a ratio of sales in the sales report. (eg Kitchen labour cost should be x% of food sales)

Now I am fine writing all the measures in DAX because I can put them in a table isolated from the Power Query tables. But calculated columns I would ideally push back to Power Query.

This is the DAX I constructed to put a calc column in the SalesReport table for the Kitchen labour costs

=CALCULATE(SUM(Timesheet[Labour Cost]), FILTER(Bridge_CompanyDate, Bridge_CompanyDate[CompanyDateKey]=SalesReports[CompanyDateKey]), FILTER(Timesheet, Timesheet[Category]="Kitchen"))

Is there a way to do similar types of aggregation in Power Query? I am tired of rewriting these calc columns when I have to reload the thing in the data model :(

Thanks

Tuesday, July 29, 2014 5:40 PM

• Hi Cathy. You can do this kind of calculation in Power Query, but it's not quite the same as in Power Pivot/DAX.

If I understand your DAX expression correctly, you're wanting the sum of the [Labour Cost] column from the Timesheet table, for all rows with a [Category] of "Kitchen", where the timesheet date matches the date of the current row in the SalesReport table. In M, this would look something like this:

= Table.AddColumn(Source, "Kitchen Labour Cost", each let expectedDate = [Date] in List.Sum(Table.SelectRows(Timesheet, each [Category] = "Kitchen" and [Date] = expectedDate)[Labour Cost]))

(Instead of going through the Bridge_CompanyDate table, I'm matching the SalesReport and Timesheet dates directly just to make the example simpler.)

Let me know if that helps.

Ehren

• Marked as answer by Friday, August 29, 2014 6:58 PM
Wednesday, August 27, 2014 8:31 PM

### All replies

• Hey Cathy.

Anybody have a suggestion for her?

Thanks!

Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Saturday, August 2, 2014 2:26 AM
• Hi Cathy. You can do this kind of calculation in Power Query, but it's not quite the same as in Power Pivot/DAX.

If I understand your DAX expression correctly, you're wanting the sum of the [Labour Cost] column from the Timesheet table, for all rows with a [Category] of "Kitchen", where the timesheet date matches the date of the current row in the SalesReport table. In M, this would look something like this:

= Table.AddColumn(Source, "Kitchen Labour Cost", each let expectedDate = [Date] in List.Sum(Table.SelectRows(Timesheet, each [Category] = "Kitchen" and [Date] = expectedDate)[Labour Cost]))

(Instead of going through the Bridge_CompanyDate table, I'm matching the SalesReport and Timesheet dates directly just to make the example simpler.)

Let me know if that helps.

Ehren

• Marked as answer by Friday, August 29, 2014 6:58 PM
Wednesday, August 27, 2014 8:31 PM
• Yes that helps tremendously, thank you!

I will try this approach with a list of variables in the let ... in portion of the statement as I need to filter on both Company and Date (hence the use of the CompanyDateKey). Based on what you've provided it shouldn't matter how many conditions I apply to the Timesheet table and I should be able to do this just fine with a variable list.

Friday, August 29, 2014 7:05 PM