Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Muniz_Felipe
Frequent Visitor

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?

Muniz_Felipe_0-1747307826191.png

 

1 ACCEPTED SOLUTION
Muniz_Felipe
Frequent Visitor

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.

Muniz_Felipe_0-1747308504992.png

 

View solution in original post

4 REPLIES 4
slorin
Super User
Super User

Hi @Muniz_Felipe 

Another solution with Table.Join

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

Stéphane 

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?

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

Muniz_Felipe
Frequent Visitor

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.

Muniz_Felipe_0-1747308504992.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.