Forum Discussion

j_w's avatar
j_w
Helper IV
9 years ago
Solved

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

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 Tab...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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')

     

  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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"

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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's avatar
    MarcelBeug
    Community Champion
    9 years ago

    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"