How to using SQL statement to create a new table base on two exiting tables?

[j_w]

There are two tables in Power BI, Table1 and Table2,

the data of Table1 comes from Database1 in Server1,

the data of Table2 comes from Database2 in Server2.

Table1 has columns: ID1, Value1

Table2 has columns: ID2, Value2

There is a one-to-one relationship between the two tables: ID1=ID2

But IDs in these two tables are not totally matched, for example:

Records in Table1:

ID1    Value1

1        A

2        B

3        C

4        D

Records in Table2:

ID2    Value2

1        A

3        C

5        E

Is it possible to create a new table Table3 in Power BI using SQL statement or other methods to get the diffrent records in Table1? All the records in Table3 come from Table1.

So Table3 should have following records:

2        B

4        D

Thanks.

[Phil_Seamark · Accepted solution]

Hi @j_w

You can do this in a number of places.  In Power BI you can create new tables using this button

And just paste in the following DAX for your example

Table 3 = EXCEPT('Table 1','Table 2')

[j_w]

@Phil_Seamark @MarcelBeug

Thank you, you are so amazing 🙂

[Phil_Seamark · Accepted solution]

Hi @j_w

You can do this in a number of places.  In Power BI you can create new tables using this button

And just paste in the following DAX for your example

Table 3 = EXCEPT('Table 1','Table 2')

[Phil_Seamark]

You can also do it in SQL but you will probably need a linked server between the two servers for the query to work.

There will also be a way you can do it in the Query Editor too @MarcelBeug will probably give you the best approach.

[MarcelBeug · Accepted solution]

In Power Query you can simply merge (join) the tables with join type left anti (records in Table1 that don't exist in Table2).

This video illustrates how the code below is created.

let
    Source = Table.NestedJoin(Table1,{"ID1"},Table2,{"ID2"},"NewColumn",JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(Source,{"NewColumn"})
in
    #"Removed Columns"