Power Query: Aggregating Data from Multiple Tables Based on Conditions

In data analysis, it’s often necessary to combine or aggregate data from multiple sources to create meaningful insights. This guide will walk you through two methods for adding aggregated data columns to a table based on filtered data from a second table. By the end of this guide, you’ll have a clear understanding of how to leverage these methods to enhance your data analysis workflows.

Instructions

We’ll use two simple data tables to demonstrate both methods. The first table, TBL_EMPLOYEES, contains a list of employees with their unique ID. The second table, TBL_SALES, holds the sales data for each employee by month.

In both methods, we import these tables in the first two query steps and name them Source_TBL_EMPLOYEES and Source_TBL_SALES.

Method 1: Using Table.SelectRows

In this method, we use Table.SelectRows with an inline condition function (sales) => sales[ID] = [ID] to add two columns: one for the total sales using List.Sum and one for the number of sales using Table.RowCount.

The inline condition checks the ID column in Source_TBL_SALES against the ID column in Source_TBL_EMPLOYEES.

This method is particularly useful when you have multiple conditions to check in Table.SelectRows. In this example, we only match on the ID column, but if you have more complex criteria (e.g., matching by both ID and specific months), this method is easier to use.

let
  Source_TBL_EMPLOYEES = Excel.CurrentWorkbook(){[Name = "TBL_EMPLOYEES"]}[Content], 
  Source_TBL_SALES = Excel.CurrentWorkbook(){[Name = "TBL_SALES"]}[Content], 
  #"Added Column - TOTAL SALES" = Table.AddColumn(
    Source_TBL_EMPLOYEES, 
    "TOTAL SALES", 
    each List.Sum(Table.SelectRows(Source_TBL_SALES, (sales) => sales[ID] = [ID])[SALES]), 
    type number
  ), 
  #"Added Column - ENTRY COUNT" = Table.AddColumn(
    #"Added Column - TOTAL SALES", 
    "ENTRY COUNT", 
    each Table.RowCount(Table.SelectRows(Source_TBL_SALES, (sales) => sales[ID] = [ID])), 
    Int64.Type
  )
in
  #"Added Column - ENTRY COUNT"

Method 2: Using Merge and Aggregate

In this method, we merge Source_TBL_EMPLOYEES and Source_TBL_SALES using Table.NestedJoin on the common ID column. As part of the merge, we also apply aggregation functions like List.Sum and Table.RowCount to calculate total sales and the number of sales for each employee.

This method works well when the tables are joined on a single condition (in this case, the common ID column). While you can match on multiple conditions, merging can be more complex because you often need to create additional helper columns for the condition checks.

let
  Source_TBL_EMPLOYEES = Excel.CurrentWorkbook(){[Name = "TBL_EMPLOYEES"]}[Content], 
  Source_TBL_SALES = Table.TransformColumnTypes(
    Excel.CurrentWorkbook(){[Name = "TBL_SALES"]}[Content], 
    {{"MONTH", Int64.Type}, {"SALES", type number}}
  ), 
  #"Merge" = Table.NestedJoin(
    Source_TBL_EMPLOYEES, 
    {"ID"}, 
    Table.Group(
      Source_TBL_SALES, 
      {"ID"}, 
      {
        {"TOTAL SALES", each List.Sum([SALES]), type number}, 
        {"ENTRY COUNT", each Table.RowCount(_), Int64.Type}
      }
    ), 
    {"ID"}, 
    "AGGREGATED SALES", 
    JoinKind.LeftOuter
  ), 
  #"Expand AGGREGATED SALES" = Table.ExpandTableColumn(
    Merge, 
    "AGGREGATED SALES", 
    {"TOTAL SALES", "ENTRY COUNT"}, 
    {"TOTAL SALES", "ENTRY COUNT"}
  )
in
  #"Expand AGGREGATED SALES"

Results

Both methods produce the same result. The tables display the correct total sales and entry count for each employee, aggregated as expected.

Summary

Both methods — using Table.SelectRows and Table.NestedJoin — provide effective ways to add aggregated data to a table based on specific criteria. The first method is more straightforward for complex criteria, while the second is better for more straightforward joins. Both approaches allow you to enrich your data with calculated values from related tables, depending on your use case.