Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
102 | |
99 | |
38 | |
37 |
User | Count |
---|---|
158 | |
125 | |
76 | |
74 | |
63 |