Data model join issue

[Anonymous]

I have two tables that join where two columns equal or one column is null.  In SQL I would construct the join as such:

Select * 

From Table_A

     join Table_B

        on (Table_A.column1 = Table_B.column1

            or Table_A.column1 is null)

             and Table_A.column2 = Table_B.column2

Is there a similar way to handle this in Power BI?  Or do I need to join these tables together through the Advanced Options with a sql statement?

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may get the table in query editor with 'Merge Queries' Function or new a table with dax. Show the sample file here for your reference.

First way in Query Edtior.

let
    Source = Table.NestedJoin(TableA,{"Column2"},TableB,{"Column2"},"TableB",JoinKind.LeftOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Column1", "Column2"}, {"TableB.Column1", "TableB.Column2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded TableB", "Custom", each if [Column1] = [TableB.Column1] then [Column1] else if [Column1] = null then null else "delete"),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] <> "delete")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

Second way in Dax.

TableA (2) =
VAR t =
    ADDCOLUMNS (
        TableA,
        "column1_B", LOOKUPVALUE ( TableB[Column1], TableB[Column2], TableA[Column2] ),
        "column2_B", LOOKUPVALUE ( TableB[Column2], TableB[Column2], TableA[Column2] )
    )
RETURN
    FILTER ( t, TableA[Column1] = [column1_B] || TableA[Column1] = BLANK () )

Regards,

Cherie