Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I'm wondering if there is a better way to be joining tables based on multiple columns.
Basically, I have 2 tables with computer names. The computer names may be in "Column A" or "Column B" or "Column C" of one table, and then the other table may have computer names in "Column A" or "Column B" or "Column C" of that table. There may also be cases where a computer name is in Column A+B, or A+B+C, or B+C, etc.
What I have been doing is joining (new) based on Table 1.ColA with Table 2.ColA, then joining (new) Table 1.ColB with Table 2.ColA, then joining (new) Table 1.ColC with Table 2.ColA, etc, and continuining until all possibilities are achieved. This is obviously a huge waste of resources and bandwidth, but I'm not sure how to do some kind of if then column scenario. I would then append all of the merges into one table which causes a ton of duplicates, but I can visually clean this up by using count(distinct) so that's not a big problem, but something that could just be done a lot better.
Any help here? Can I provide any more information to help? Exampe tables below.
I only started power bi about a week ago so I'm rather new!
I have examples of tables below. I also wouldn't want to match on "nulls", but that's a very small problem.
Table1
ColA | ColB | ColC |
computername | null | null |
null | computername2 | null |
null | null | computername3 |
computername4 | computername4 | null |
Table2
ColA | ColB | ColC |
null | computername | null |
computername2 | null | null |
null | computername3 | null |
null | computername4 | computername4 |
Solved! Go to Solution.
Try this code to add a new table to your report:
Table 3 =
VAR _A =
UNION (
VALUES ( 'Table 1'[ColA] ),
VALUES ( 'Table 1'[ColB] ),
VALUES ( 'Table 1'[ColC] )
)
VAR _AA =
FILTER ( SUMMARIZE ( _A, [ColA] ), [ColA] <> "null" )
VAR _B =
UNION (
VALUES ( 'Table 2'[ColA] ),
VALUES ( 'Table 2'[ColB] ),
VALUES ( 'Table 2'[ColC] )
)
VAR _BB =
FILTER ( SUMMARIZE ( _B, [ColA] ), [ColA] <> "null" )
RETURN
SUMMARIZE ( UNION ( _AA, _BB ), [ColA] )
Output:
Download the sample file attached.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Try this code to add a new table to your report:
Table 3 =
VAR _A =
UNION (
VALUES ( 'Table 1'[ColA] ),
VALUES ( 'Table 1'[ColB] ),
VALUES ( 'Table 1'[ColC] )
)
VAR _AA =
FILTER ( SUMMARIZE ( _A, [ColA] ), [ColA] <> "null" )
VAR _B =
UNION (
VALUES ( 'Table 2'[ColA] ),
VALUES ( 'Table 2'[ColB] ),
VALUES ( 'Table 2'[ColC] )
)
VAR _BB =
FILTER ( SUMMARIZE ( _B, [ColA] ), [ColA] <> "null" )
RETURN
SUMMARIZE ( UNION ( _AA, _BB ), [ColA] )
Output:
Download the sample file attached.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
User | Count |
---|---|
82 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
105 | |
97 | |
86 | |
68 |