Crossjoin lists in Power Query

[Muniz_Felipe]

Hello!

I have two lists: one with the months, from 1 to 12 and other with 5 years, from 2021 to 2025.

My idea is to build a small table with all the months in one column and each row in the second column should have the years list, so that I can expand everything at once and have each month repeated 5 times (once per year).

I know I can convert the first list to table and then add a row etc. but I don't want to do that. I want to work with nested lists. What I want to achieve is something like a crossjoin we do in DAX, but in Power Query.

I tried to work with Table.FromColumns( ), but that didn't work as expected, as you can see below.

Does anybody have any idea on what I could do?

[slorin]

Hi @Muniz_Felipe 

Another solution with Table.Join

= Table.Join(Table.FromColumns({{1..12}}, {"Month"}), {}, Table.FromColumns({{2021..2025}}, {"Year"}), {})

Stéphane 

[Muniz_Felipe]

Hi, @slorin. I noticed you used { } as keys for the table.join function. Also, if the joining type is not specified the function will use inner join as standard. Since there's no common columns between the two tables/lists and no keys were passed, how do these things deliver a cross join between the two tables?

[Muniz_Felipe]

Interesting! That's another cool solution for this in a very simple way. Thanks!

[Muniz_Felipe · Accepted solution]

I've just found out I can do that by using List.Repeat with one of my lists. For example, in the formula below I used List.Repeat to repeat the year list 12 times, one for each month. And then the function Table.FromColumns transformed my two lists into a table.

Now, if I expand the column2, I'll have my crossjoined lists in Power Query.