How to Find Missing Customer-Product Combinations with Power Query Left Join in Excel
Question details
The user needs to identify unowned products for customers using Power Query, but a standard left join cannot return missing customer-product combinations as null values.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Attempting to merge an owned-products table with a master products table to highlight which products a customer is missing.
- Observed behavior
- The left join only returns existing matches from the left table. It fails to display missing unowned products because those rows do not exist in the original customer-product table.
Ensure you have your master 'Products' table and your 'Owned Products' (Customers) table loaded into the Power Query Editor as separate queries before attempting the merge.
Create a Cross Join Grid Before the Left Join
By generating a complete Cartesian product of all customers and all products first, you create a comprehensive left table. Joining this to your existing data will correctly expose missing items as nulls.
A left join can only keep rows that already exist in the left table. If you want to see missing products for a customer, you must first construct a 'perfect' table where every customer is paired with every possible product.
Reference your 'Owned Products' table. Select the 'Customer' column, right-click the header, and choose 'Remove Duplicates' to create a unique list of all customers.
Navigate to 'Add Column' > 'Custom Column' for your unique customer list. Name the column 'Key' and enter the formula `1`. Repeat this exact process for your master 'Products' table.
From the Home tab, click 'Merge Queries as New'. Select your customer table as the first table and the products table as the second. Click the 'Key' column in both tables and select 'Inner Join'. Once merged, delete the 'Key' columns.
Click 'Merge Queries' on your newly created cross-join grid. Select your original 'Owned Products' table as the second query. Hold CTRL to select both the 'Customer' and 'Product' columns in both tables, and choose 'Left Outer Join'.
Click the expand icon on the newly merged column. Any rows displaying 'null' in the expanded data indicate a product that the corresponding customer does not own.
Looking for a Lightweight, Powerful Excel Alternative?
If you regularly process complex datasets and require a fast, reliable spreadsheet tool, try WPS Office. It provides robust data analysis capabilities and flawless compatibility with Microsoft Excel without the heavy subscription costs.
Frequently Asked Questions
Why doesn't a standard Left Join show missing data in Power Query?
A Left Join strictly retains the rows that already exist in the left table. If a specific customer-product combination doesn't exist in your base data, the join process cannot generate a new row to display a null; it simply returns the existing rows alongside their matches.
What is a cross join (Cartesian product) in Power Query?
A cross join pairs every single row from the first table with every single row from the second table. In Power Query, this is typically achieved by adding a temporary dummy column with the same value (like '1') to both tables and joining them on that column.
How can I filter the final query to only display the missing products?
After expanding the columns from your Left Outer Join, locate the column that pulls data from your 'Owned Products' table. Click the filter dropdown on the column header and select only '(null)'. This will hide owned products and leave only the missing combinations.
