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
- Open your Power BI Desktop and load both tables into your Data Model.
- Go to “Home”, then select “Transform Data” to open Power Query Editor.
- Select the “Employees” table in the Queries pane.
- Click on the “Merge Queries” button in the “Home” tab.
- 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.
- 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.
- 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.
- First Check data types: In Power Query, click on the column header icon and ensure matching columns have the same data type.
- Clean the data: Use the “Transform” tab to trim whitespace, change case, or replace values to ensure consistency.
- Joined values not matching: If rows are missing after the merge, verify that both ID and Department match exactly in both tables.
- For performance issues with large datasets, consider using matching the data using index columns rather than using text columns.