Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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?
Solved! Go to 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.
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!
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |