CROSS JOIN 2 or more columns in Power Query

In this blog let's take a look at how to create cartesian product of 2 or more columns/lists in PowerQuery with UI, List Functions and Recursion when number of columns aren't fixed.

Generate the data using the following code:

let
    A = Table.FromColumns ( {{1..4}}, type table [Num = Int64.Type] ),
    B = Table.FromColumns ( {{"Jan", "Feb"}}, type table [Month = text] )
in
    B

Let's assume there are 2 tables in your model A and B now you want to do a cartesian product of these 2, one way of doing this is by using Table.AddColumn function where you add 1 table into each row of another table and then expand it to perform crossjoin.

let
    ...,
    C = Table.AddColumn ( A, "B", each B, type table ),
    D = Table.ExpandTableColumn ( C, "B", {"Month"}, {"Month"} )
in
    D

Which returns this:

For 3 tables we can use Table.AddColumn twice:

let
    A = Table.FromColumns ( {{1..4}}, {"Num"} ),
    B = Table.FromColumns ( { {"Jan", "Feb"} }, {"Month"} ),
    C = Table.FromColumns ( 
        {{"Apple", "Banana", "Watermelon"}}, {"Fruit"} 
    ),
    Add_C = Table.AddColumn ( A, "B", each B, type table ),
    Add_D = Table.AddColumn ( Add_C, "C", each C, type table ),
    Expand_B = Table.ExpandTableColumn ( 
        Add_D, "B", {"Month"}, {"Month"} 
    ),
    Expand_C = Table.ExpandTableColumn ( 
        Expand_B, "C", {"Fruit"}, {"Fruit"} 
    )
in
    Expand_C

Which returns (4 * 2 * 3) combinations.

So that's how you can do with adding table in each row and then expanding it but to me this isn't the most elegant solution as it involves manually adding columns again and again.

Alternate of Tables is List object which has awesome functions such as List.Transform or List.TransformMany.

First let's see how to do this with List.Transform, we start with 2 lists:

let
    A = { 1..4 },
    B = { "Jan", "Feb" }
in
    B

The idea is to iterate one list {x} and inside that iterate another list {y} and then return a list containing { x, y }

let
    ...,
    C = List.Transform ( 
        A, 
        (x) => List.Transform ( B, (y) => {x, y } ) 
    ) ,
    D = Table.FromRows ( 
        List.Combine ( C ), 
        type table [Num = Int64.Type, Month = text] 
    )
in
    D

Which returns 8 combinations:

So what about 3 Lists? We will have to nest List.Transform and List.Combine to achieve the crossjoin:

let
    A = { 1 .. 4 },
    B = { "Jan", "Feb" },
    C = { #date ( 2025, 01, 01 ), #date ( 2026, 01, 01 ) },
    D = List.Transform (
            A, 
            (x) =>
                List.Combine (
                    List.Transform ( 
                        B, 
                        (y) => 
                        List.Transform ( 
                            C, 
                            (z) => { x, y, z } 
                        )
                    )
                )
        ),
    E = Table.FromRows (
        List.Combine ( D ),
        type table [ Num = Int64.Type, Month = text, Date = date ]
    )
in
    E

This returns 16 combinations but as you can see the readability is reduced and if you were to return back to it months later you might have a hard time understanding it.

To improve readability we can use List.TransformMany which does the operation of 2 List.Transform in one single function

let
    ...,
    D = List.TransformMany ( A, (x) => B, (x, y) => { x, y } ),
    E = Table.FromRows ( D, type table [Num = Int64.Type, Month = text] )
in
    E

This returns the combination of Nums and Months, now to add another combination of Dates we can wrap another List.TransformMany.

let
    A = { 1 .. 4 },
    B = { "Jan", "Feb" },
    C = { #date ( 2025, 01, 01 ), #date ( 2026, 01, 01 ) },
    D = List.TransformMany ( 
        List.TransformMany ( A, (x) => B, (x, y) => { x, y } ),
        (x) => C,
        (x, y) => List.Combine ( { x, {y} } )
    ),
    E = Table.FromRows ( 
        D, 
        type table [Num = Int64.Type, Month = text, Date = date] 
    )
in
    E

And it returns the same result as using 3 nested List.Transform.

Alright so what about 4 columns, 5 columns? Now things are getting out of hand because you will have to keep adding more List.TransformMany.

But, we can avoid that and use only 1 List.TransformMany, how exactly? Recursion! M has Recursion capabilities so we can create a function that iterates n number of columns that we provide and produce a cartesian product, and you will see the last example will come in very handy.

First we need to create a Function that accepts the columns that we're going to provide as a list, I will name this function as FxCrossJoin:

( Columns as list ) as list =>

Then we will create a nested function that will take 2 columns in each iteration and by using List.TransformMany it will produce cartesian product.

( Columns as list ) as list =>
    let
        CrossJoin = ( A as list, B as list ) as list =>
            List.TransformMany (
                List.RemoveNulls ( A ),
                ( x ) => List.RemoveNulls ( B ),
                ( x, y ) =>
                    if Value.Is ( x, List.Type ) 
                    // ^ from second cartesian product onwards when 
                    // we start sending A & B in form of 
                    // { { CROSSJOIN of previous 2 }, {"A", "B" } )

                    then List.Combine ( { x, { y } } ) 
                    // ^ Combine if there are list and scalar values
                    
                    else { x, y } 
                    // ^ For the first 2 columns only
            )

Next we need to call this logic from a recursive function and for that we use:

Recurse = ( Columns as list ) as list =>
    if List.Count ( Columns ) = 0 
    then {}
    else 
        if List.Count ( Columns ) = 1 
        then Columns{0}
        else
            @Recurse (
                List.Combine (
                    { 
                        { CrossJoin ( Columns{0}, Columns{1} ) }, 
                        List.Skip ( Columns, 2 ) 
                    }
                )
            )

So what is happening here? Every recursive function needs start and exit and it can not run forever because there is limitation on how many times you can stack the calls to the save function during recursion, and to respect that we need to use if statements to decide when to exit the loop.

if List.Count ( Columns ) = 0 
then {}

The above checks if the users has passed an empty list then just return an empty list.

if List.Count ( Columns ) = 1 
then Columns{0}

If the count of list is 1 then just return that list.

@Recurse (
    List.Combine (
        { 
            { CrossJoin ( Columns{0}, Columns{1} ) }, 
            List.Skip ( Columns, 2 ) 
        }
    )
)

For scenarios where we have lists that aren't empty this function will call itself until it reduces all the lists into a single list of list that contains all the combinations.

List.Skip part basically removes those lists that have been utilized for crossjoin in the current iteration.

To easily visualize this recursion you can use the followingquery where I have shown how recursive logic is implemented.

let
    Source = {
        { "a", "b", "c" }, 
        { "Jan", "Feb" }, 
        { 10, 20 }, 
        { #date ( 2025, 01, 01 ), #date ( 2026, 01, 01 ) }
    }, 
    CrossJoin = ( A as list, B as list ) as list =>
        List.TransformMany (
            List.RemoveNulls ( A ), 
            ( x ) => List.RemoveNulls ( B ), 
            ( x, y ) =>
                if Value.Is ( x, List.Type ) 
                then List.Combine ( { x, { y } } ) 
                else { x, y }
        ), 
    First = List.Combine ( 
        { 
            { CrossJoin ( Source{0}, Source{1} ) }, 
            List.Skip ( Source, 2 ) 
        } 
    ), 
    Second = List.Combine ( 
        { 
            { CrossJoin ( First{0}, First{1} ) }, 
            List.Skip ( First, 2 ) 
        } 
    ), 
    Third = CrossJoin ( Second{0}, Second{1} ), 
    ToTable = Table.FromRows ( Third )
in
    ToTable

Complete code of recursion, the function is named as FxCrossJoin

( Columns as list ) as list =>
    let
        CrossJoin = ( A as list, B as list ) as list =>
            List.TransformMany (
                List.RemoveNulls ( A ),
                ( x ) => List.RemoveNulls ( B ),
                ( x, y ) =>
                    if Value.Is ( x, List.Type ) 
                    then List.Combine ( { x, { y } } ) 
                    else { x, y } 
            ),
        Recurse = ( Columns as list ) as list =>
            if List.Count ( Columns ) = 0 
            then {}
            else 
                if List.Count ( Columns ) = 1 
                then Columns{0}
                else
                    @Recurse (
                        List.Combine (
                            { 
                                { CrossJoin ( Columns{0}, Columns{1} ) }, 
                                List.Skip ( Columns, 2 ) 
                            }
                        )
                    )
    in
        Recurse ( Columns )

Usage:

let
    Source = {
        { "a", "b", "c" },
        { "Jan", "Feb" },
        { 10, 20 },
        { #date ( 2025, 01, 01 ), #date ( 2026, 01, 01 ) }
    },
    Crossjoin = FxCrossJoin ( Source ),
    ToTable = Table.FromRows (
        Crossjoin,
        type table [ 
            Text = text,
            Month = text, 
            Num = Int64.Type, 
            Date = date 
        ]
    )
in
    ToTable

Result: