none
Aggregating across many-to-many relationship in Power Query? RRS feed

  • 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

Answers

  • 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 Cathy Dumas - Friday, August 29, 2014 6:58 PM
    Wednesday, August 27, 2014 8:31 PM
    Owner

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)

    Answer an interesting question? Create a wiki article about it!

    Saturday, August 2, 2014 2:26 AM
    Owner
  • 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 Cathy Dumas - Friday, August 29, 2014 6:58 PM
    Wednesday, August 27, 2014 8:31 PM
    Owner
  • 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