Forum Discussion
Crossjoin lists in Power Query
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?
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.
4 Replies
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.
slorinSuper User
Hi Muniz_Felipe
Another solution with Table.Join
= Table.Join(Table.FromColumns({{1..12}}, {"Month"}), {}, Table.FromColumns({{2021..2025}}, {"Year"}), {})Stéphane
Interesting! That's another cool solution for this in a very simple way. Thanks!
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?