How To Perform CROSSJOIN In Power Query & Power BI

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.



Posted in Blog and tagged , , , .

Leave a Reply

Your email address will not be published. Required fields are marked *