Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 2 tables like
Table1 = #table({"column1"},{{"1"},{"2"},{"3"}})
Table2 = #table({"column1"},{{"a"},{"b"}})
I would like to get this result
Table3 = #table({"column1","Column2"},{{"1","a"},{"2","a"},{"3","a"},{"1","b"},{"2","b"},{"3","b"}})
what is the best approach with M?
thank you
m
Solved! Go to Solution.
Hi @maclura ,
This should work:
Go to PowerQuery, in both tables create a column which equals 1 for every row:
Go to merge queries, select the columns with 1 for every row and choose full outer join.
Expand column 2, this will be the result:
If I answered your question, please mark it as a solution to help other members find it more quickly.
Another way to do this is to define a custom column "column2" on Table1 as Table2[column1].
Then click the expand icon in the upper right corner of the above.
Another way to do this is to define a custom column "column2" on Table1 as Table2[column1].
Then click the expand icon in the upper right corner of the above.
Thank you @AlexisOlson , it does work and it's even more elegant and versatile compared to the previous solution. You can choose if to expand the second column in new rows, or replicate the merging of the values of the second table in each row of the first table. That's powerful!
Thank you so much
Hi @maclura ,
This should work:
Go to PowerQuery, in both tables create a column which equals 1 for every row:
Go to merge queries, select the columns with 1 for every row and choose full outer join.
Expand column 2, this will be the result:
If I answered your question, please mark it as a solution to help other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.