Joining Tables with multiple columns

[admincaleb007]

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

[VahidDM · Accepted solution]

Hi @admincaleb007 

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/