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 = Ta...
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

     

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