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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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