How to merge tables based on two columns in Power BI?

User Question: “How can I join two tables in Power Query based on matching values in two different columns?”

Joining tables based on matching values in Power Query

Data Preparation

We will use a data model that includes the following tables:

  • Table “Employees” (ID: Integer, FirstName: Text, LastName: Text, Department: Text)
  • Table “Salaries” (EmployeeID: Integer, Department: Text, Salary: Currency)

Step-by-step instructions

  1. Open your Power BI Desktop and load both tables into your Data Model.
  2. Go to “Home”, then select “Transform Data” to open Power Query Editor.
  3. Select the “Employees” table in the Queries pane.
  4. Click on the “Merge Queries” button in the “Home” tab.
  5. In the Merge dialog:
    • Select “Salaries” as the table to merge with.
    • Choose the fields “ID” from Employees and “EmployeeID” from Salaries.
    • Hold Ctrl and select “Department” from both tables.
    • Choose “Left Outer” join kind.
    • Click OK.
  1. Expand the new column:
    • Click the expand button (two arrows) in the new column header.
    • Select only the “Salary” column.
    • Uncheck “Use original column name as prefix”.
    • Click OK.
  1. Rename the merged query to “EmployeeSalaries”.

Note that you can join two or multiple tables according to more complex criteria as needed.

For completeness here’s the M code for the joining of the two tables based on the Employee ID field:

let
    Source = Employees,
    MergedQuery = Table.NestedJoin(Source, {"ID", "Department"}, Salaries, {"EmployeeID", "Department"}, "Salaries", JoinKind.LeftOuter),
    ExpandedSalaries = Table.ExpandTableColumn(MergedQuery, "Salaries", {"Salary"}, {"Salary"})
in
    ExpandedSalaries

Troubleshooting

Common issues include mismatched data types (e.g., text vs. number for ID columns for example). Here are a few things that can go wrong and how to solve them.

  1. First Check data types: In Power Query, click on the column header icon and ensure matching columns have the same data type.
  2. Clean the data: Use the “Transform” tab to trim whitespace, change case, or replace values to ensure consistency.
  3. Joined values not matching: If rows are missing after the merge, verify that both ID and Department match exactly in both tables.
  4. For performance issues with large datasets, consider using matching the data using index columns rather than using text columns.