How to Create a Dynamic Cartesian Product in Excel Power Query
Question details
The user needs to generate every possible combination of two dynamically changing Excel lists without manually copying and pasting rows.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Combining a list of warehouse sites and a list of item numbers into a comprehensive matrix for pricing calculations and lookup tables.
- Observed behavior
- The goal is to set up a Cartesian product that automatically updates with all new site-item combinations whenever the source lists are modified.
Ensure both of your source lists are formatted as standard Excel Tables (select your data and press Ctrl+T). This allows Power Query to treat them as dynamic data sources that automatically expand when new rows are added.
Use Power Query Custom Columns to Merge and Cross-Join
By adding a matching temporary constant column to both queries, you can merge them together to instantly generate a full Cartesian product of all items and sites.
A Cartesian product (or cross-join) pairs every row from one table with every row from another table. Since Power Query requires a matching column to perform a merge, creating a dummy column with a constant value in both tables serves as the bridge to connect them.
Click anywhere inside your first table (e.g., Sites) and go to Data > From Table/Range. In the Power Query Editor, go to Add Column > Custom Column. Name the column 'JoinKey' and enter '1' in the formula box. Click Home > Close & Load To..., and choose 'Only Create Connection'.
Click inside your second table (e.g., Items) and go to Data > From Table/Range. Just like before, go to Add Column > Custom Column, name it 'JoinKey', and enter '1' in the formula box.
While still in the Power Query Editor for the second list, go to the Home tab and click Merge Queries. In the dialog box, select your first query (Sites) from the dropdown menu. Click on the 'JoinKey' column in both preview windows to highlight them, and click OK.
A new column containing tables will appear. Click the Expand icon (two diverging arrows) in the header of this new column. Uncheck 'Use original column name as prefix' and click OK to reveal all combinations. You can now right-click and remove the 'JoinKey' column.
To aid in VLOOKUPs, go to Add Column > Custom Column and enter a concatenated formula, such as =[Site] & "-" & [Item]. Click Home > Close & Load to output the final matrix to your worksheet.
Experience Seamless Excel Compatibility with WPS Office
While advanced Power Query editor tasks are handled natively in Microsoft Excel, WPS Office provides an incredibly lightweight, fully compatible, and free alternative for managing complex spreadsheets, data lookups, and everyday calculations without bogging down your computer.
Frequently Asked Questions
Why do I need to create a custom 'JoinKey' column in both queries?
Power Query requires two tables to share at least one matching column in order to perform a merge. By adding a constant value (like '1') to a custom column in both queries, you force every row in the first table to match with every row in the second table, creating a full Cartesian product.
How do I update the combinations when new data is added?
Because the source data is formatted as Excel Tables, you don't need to rebuild the query. Just add your new sites or item numbers to the bottom of the source lists, then navigate to the Data tab and click Refresh All.
Can I view the steps I took to build this cross-join later?
Yes. Navigate to Data > Queries & Connections. Right-click your merged query in the side pane and select Edit. The Power Query Editor will open, displaying all your actions chronologically under the 'Applied Steps' panel on the right side.
