How to Create a Cross Join in Power Query When Merge Is Disabled
Question details
The user needs to create every possible combination of rows from two tables (a cross join or Cartesian product) in Power Query, especially when the standard Merge Queries option is disabled or lacks a matching condition.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Attempting to merge two tables without a matching key column or when the merge button is completely inactive.
- Observed behavior
- The standard merge function cannot be used natively without a common key. The goal is to safely generate a Cartesian product of both tables.
Ensure both tables you want to join are already loaded into your Power Query Editor as connections. Be highly cautious with large datasets, as cross joins mathematically multiply row counts (e.g., 100 rows × 1,000 rows = 100,000 rows) and can severely impact performance.
Use a Custom Column to Create a Cross Join
This is the most straightforward method to create a Cartesian product. It works by adding the entire second table as a custom column to every row of the first table, and then expanding it.
A cross join returns all possible combinations of rows from two or more tables. Because standard joins require a matching condition, bypassing this limitation involves using Power Query's ability to embed entire tables into single rows.
Launch your Power Query Editor and select your primary table from the Queries pane on the left side.
Navigate to the 'Add Column' tab on the top ribbon and click on the 'Custom Column' button.
In the Custom Column dialog box, give your new column a descriptive name. In the 'Custom column formula' box, type the exact name of your secondary query table (e.g., = Table2) and click 'OK'.
Locate the newly created custom column in your data preview. Click the 'Expand' icon (two diverging arrows) located on the right side of the column header.
Uncheck 'Use original column name as prefix' if desired, select the specific columns you want to include from the second table, and click 'OK' to generate all combinations.
Try WPS Office for Lightweight Data Processing
If you frequently experience slow loading times or crashes when handling complex queries or massive datasets in Microsoft Excel, consider switching to WPS Office. It provides a highly compatible and lightweight spreadsheet alternative designed to run smoothly on any device.
- 1. Download WPS Office: Visit the official WPS website to download the free WPS Office suite for your operating system.
- 2. Install and Launch: Follow the quick installation steps and open the WPS Spreadsheet application.
- 3. Open Your Spreadsheets: Directly open your existing Excel workbooks and utilize built-in data tools for fast analysis without complex setups.
Frequently Asked Questions
What exactly does a cross join or Cartesian product do?
A cross join pairs every single row in the first table with every single row in the second table. It ignores any matching logic or primary keys, outputting absolutely every possible combination between the datasets.
Why is my Power Query cross join taking so long to load?
Because a cross join multiplies the rows of the two tables. Joining a table of 1,000 rows with a table of 10,000 rows produces 10,000,000 combinations. To improve performance, filter both tables to include only the necessary rows before expanding the custom column.
Why is the Merge Queries button disabled in Power Query?
The Merge Queries button might be disabled if your data sources have unresolved privacy level conflicts, if you are selecting incompatible column types, or if you haven't loaded the queries correctly into the editor.
