Get Records from Table A that are not in Table B

Hi,

I am new to Power BI and SQL Queries (therefore apologies if my question seems very basic) ... I am trying to populate a table to show all records from Table A that are not in Table B and am not sure how to achieve this.

Online there are lots of different websites however all the different versions of code I am trying doesn't seem to work.

If someone could please help that would be fab.

Thanks,

Amie.

kaushikd
9 years ago
Hi Amie-Louise
I have used your two dataset as 'Table1(Lead Website)' and 'Table1(Database)'

Here is your DesiredTable you want

Please Note
You have to create a Dummy Table which will hold the Intersect records of 'Table1(Lead Website)' and 'Table1(Database)'

DummyTable Screenshot

Steps:-
1. Switch to the Data View
2. Go to the Modelling Tab and choose New Table.
3. Fire the query
DummyTable = INTERSECT('Table1(Lead Website)','Table1(Database)')
4. Again Choose New Table
5. Fire the Query
DesiredTable = EXCEPT('Table1(Lead Website)',DummyTable)

If this is what you want then
Please give Kudos and Accept this as a solution

7 Replies

Eric_Zhang
Microsoft Employee
9 years ago
Amie-Louise

What are those two tables' schema like?

If they have the same column, you can use EXCEPT in SQL or in DAX.

SQL SELECT column1,column2,column3 FROM Table1 EXCEPT SELECT column1,column2,column3 FROM Table2 DAX difference Table = EXCEPT(Table1,Table2)

If they have different columns and the duplication is identified by some key column, say ID

SQL SELECT * FROM Table1 T1 WHERE NOT EXISTS(SELECT 1 FROM Table2 t2 WHERE t2.id=t1.id ) DAX difference Table = FILTER(Table1,NOT(CONTAINS(Table2,Table2[ID],Table1[ID])))
- Amie-Louise
  New Member
  9 years ago
  Hi,
  
  Thanks for coming back to me so quickly on this, so as an example the tables look like this:-
  
  Table 1 (Lead Website)
  Name
  Address
  Telephone Number
  Mr Smith
  123 Main Street
  123456
  Mr Jones
  456 High Street
  789101
  Mrs Peacock
  1 London Road
  112131
  
  Table 1 (Database)
  Name
  Address
  Telephone Number
  Mr Smith
  123 Main Street
  123456
  Mrs Peacock
  1 London Road
  112131
  Dr Jackson
  20 Roman Close
  415161
  Miss Poppy
  4 Ash Crescent
  718192
  
  From the above we would like to run a query to return the leads that are not on the database. In this case it would be Mr Jones.
  
  Does that help?
  
  Thanks,
  Amie.
  - kaushikd
    Resolver II
    9 years ago
    Hi Amie-Louise
    I have used your two dataset as 'Table1(Lead Website)' and 'Table1(Database)'
    
    Here is your DesiredTable you want
    
    Please Note
    You have to create a Dummy Table which will hold the Intersect records of 'Table1(Lead Website)' and 'Table1(Database)'
    
    DummyTable Screenshot
    
    Steps:-
    1. Switch to the Data View
    2. Go to the Modelling Tab and choose New Table.
    3. Fire the query
    DummyTable = INTERSECT('Table1(Lead Website)','Table1(Database)')
    4. Again Choose New Table
    5. Fire the Query
    DesiredTable = EXCEPT('Table1(Lead Website)',DummyTable)
    
    If this is what you want then
    Please give Kudos and Accept this as a solution
- Anonymous
  Not applicable
  5 years ago
  This was the best answer. Not sure why you would need to do an INTERSECT first (another answer). This worked for me. Thanks!
- Anonymous
  Not applicable
  4 years ago
  Best Answer
  Thank you