Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have to combine a colum from 3 separate tables into one table with all combinations :
So this must be the end result
So it must only be the combinations which are present in the data, NOT ALL possible combinations, so CROSSJOIN alone doesn't do the job in my opinion.
from table 1 | from table 2 | from table 3 |
A | A | A |
B | B | null |
C | null | C |
null | D | D |
E | null | null |
null | F | null |
null | null | G |
Hi @Artefact ,
Here are the steps you can follow:
1. Create an Index for each table..
In Power Query -- add Column – Index Column – From 1.
2. Create calculated table.
Table =
var _table=
SUMMARIZE(
'Table1','Table1'[Index],'Table1'[from table 1],
"from table 2",LOOKUPVALUE('Table2'[from table 2],'Table2'[Index],'Table1'[Index]),
"from table 3",LOOKUPVALUE('Table3'[from table 3],'Table3'[Index],'Table1'[Index]))
return
SUMMARIZE(
_table,[from table 1],[from table 2],[from table 3])
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
tb1 | tb2 | tb3 |
A | A | A |
B | B | C |
C | D | D |
E | F | G |
Hello Liu,
Thanks for your reply. Almost done, but I presented my initial tables somewhat different as intended. The null values aren't exactly on the rows as in the first post. The null values are not there at all, I just wanted to show the nulls in the end result. So the table above is the right starting point for obtaining the result table. Hope thsi helps, and thanks again for your efforts!
hi, @Artefact
try to use intersect() function
you nedd intersect twice for combine 3 table
addendum :
So basetable 1 has a column with only ABCE, table 2 ABDF and table 3 ACDG.
I don't want a cartesian product with CROSSJOIN, that would result in a much bigger table
Ok, now I get what you want. You want a Full Outer Join.
Unfortunately, DAX does not support this kind of join operation as far as I'm aware, so if you can't push this operation to Power Query (which indeed supports Full Outer Joins Full outer join - Power Query | Microsoft Learn) then the only solution that comes to my mind is a not so elegant workaround, which is the following:
Let me know if it works for you.
Thanks Lorenzo, seems like your idea works, but I need the full code for your solutions. I don't understand were I put the UNION and DISTINCT functions. Seems like these functions don't work with 2 (distinct) or 3 (union) columns in my code.
Hi, it's not very clear what is the starting point and what is the end result.
What is the criteria to join the table? You are saying in the same message that you expect all possible combination and right after that you do not want all possible combinations, so it quite hard to understand what you are looking for.
The result is the table in the post
show what result you expect
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
127 | |
109 | |
93 | |
70 | |
67 |