Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Data model join issue

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?

 

 

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

     

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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