Ed Hansberry shows how to perform a conditional join in Power Query:
I am going to use two tables for this example, Fact.Sales and Dim.Customer. I only want my Fact Sales table to have customer info for the Buying Group Tailspin Toys. That info is in the Dim Customer table. I can do this several ways.
1. Do the merge, expand the Buying Group column from the Customer table, then filter to only show those rows.
2. Pre-filter the Customer table for Tailspin Toys in the Buying Group column, then do the merge. It would need to be an Inner Join, otherwise you will get nulls in the Sales table when you expand, and then you have to filter those out. Which you can do, but it is more steps.
3. You can bypass all of that and do it in one step.I’m not going to bore you with how to do methods 1 and 2. Let’s do method 3, a conditional join!
Read on for the approach, which also can take advantage of query folding when possible.