Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to perform CROSSJOIN in Power Query and Power BI DAX. Let’s get started
CROSSJOIN returns a table that contains the Cartesian product of rows from all tables in the arguments. The columns in the new table are all the columns in all the argument tables
CROSSJOIN DAX Syntax =CROSSJOIN(table1, [table2], …)
CROSS JOIN: Power Query Method
In the captions below, we have two queries in the Power Query Editor. The first table is named Data_tbl1 while the second query is named Data_tbl2 respectively
In the first query, we have 3 by 10 columns and rows. In the second query, we have 3 by 4 columns and rows respectively. What we intend to do is to find a way to cross join the two tables so that we can have all possible outcomes of all the values in both tables.
To cross join:
In the General group of the Add Column tab, click on Custom Column
In the Custom column dialogue box, execute the formula: =Data_tbl2
The formula is the name of the second query/table
Click OK
In the caption above, we have the added custom column with table to expand and extract data from
Next, click on the double upward pointed black arrows to expand
Uncheck use original column name as prefix
Click OK
In the caption above, we have all the possible outcomes based on the two queries. We have a total of 6 columns and 40 rows
CROSS JOIN: Power BI DAX Approach
We have loaded the queries into the data model as seen in the caption below
In the Calculations group of the Modelling tab, click on New Table
Table = CROSSJOIN(Data_tbl1,Data_tbl2)
The input value for the first argument is Data_tbl1, while Data_tbl2 represent the input value for the second argument.
In the capture above, we have 6 columns and 40 rows of possible cross join outcomes.