topic Re: Get Records from Table A that are not in Table B in Developer

Get Records from Table A that are not in Table B

Amie-Louise — Wed, 08 Mar 2017 14:08:35 GMT

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.

Re: Get Records from Table A that are not in Table B

Eric_Zhang — Thu, 09 Mar 2017 06:58:13 GMT

@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])))

Re: Get Records from Table A that are not in Table B

Amie-Louise — Fri, 10 Mar 2017 14:53:06 GMT

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.

Re: Get Records from Table A that are not in Table B

Eric_Zhang — Sun, 12 Mar 2017 08:55:22 GMT

@Amie-Louise

You can also try Power Query

let 
 
    LeadWebsite=  Table.FromRows({{"Mr Smith", "123 Main Street", "123456"} , {"Mr Jones", "456 High Street", "789101"},{"Mrs Peacock","1 London Road","112131"}}, {"Name", "Address", "Telephone Number"}),

    DataBase=Table.FromRows({{"Miss Poppy","4 Ash Crescent","718192"},{"Mr Smith", "123 Main Street", "123456"} , {"Dr Jackson", "20 Roman Close", "415161"},{"Mrs Peacock","1 London Road","112131"}}, {"Name", "Address", "Telephone Number"}),
     
    RemovedRowsList = Table.ToRecords(DataBase), 

    FilteredLeadWebsite= Table.RemoveMatchingRows(LeadWebsite,RemovedRowsList)

in
    FilteredLeadWebsite

Re: Get Records from Table A that are not in Table B

kaushikd — Sun, 12 Mar 2017 12:26:50 GMT

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

Re: Get Records from Table A that are not in Table B

Amie-Louise — Tue, 14 Mar 2017 09:44:56 GMT

Thank you so much for sharing the below.

All working perfectly now 🙂

Re: Get Records from Table A that are not in Table B

Anonymous — Wed, 16 Sep 2020 21:51:51 GMT

This was the best answer. Not sure why you would need to do an INTERSECT first (another answer). This worked for me. Thanks!

Re: Get Records from Table A that are not in Table B

Anonymous — Mon, 02 May 2022 12:14:51 GMT

Best Answer
Thank you