topic Re: How do I get data in table A that is not in table B, but by only looking at one specific column? in Developer

How do I get data in table A that is not in table B, but by only looking at one specific column?

Amie-Louise — Tue, 21 Mar 2017 16:36:37 GMT

Question Raised Previously:-

How do I get records from Table A that are not in Table B?

Answer:-

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

When I have tested this it works however when I use this with live data it doesn't work because not all fields are exactly the same.

E.g.

Table A Record (Lead Website)

Name = Mrs S Smith

Address = 1 Test Road, Test, TE5 73D

Email = smith@test.com

Table B Record (Database)

Name = Mrs Sandra Smith

Address = 1 Test Road, TE5 73D

Email = smith@test.com

The constant in both would be the email address, therefore is there possibly a way for us to tweak the answer above slightly to encorporate this?

Thanks,

Amie.

Re: How do I get data in table A that is not in table B, but by only looking at one specific column?

Eric_Zhang — Thu, 23 Mar 2017 08:03:59 GMT

@Amie-Louise

Power Query would be a more proper approach. Check

let  
    LeadWebsite=  Table.FromRows({{"Mrs S Smith", "1 Test Road, Test, TE5 73D", "smith@test.com"} , {"Mr Jones", "456 High Street", "789101"}}, {"Name", "Address", "Email"}),

    DataBase=Table.FromRows({{"Mrs Sandra Smith", "1 Test Road, Test, TE5 73D", "smith@test.com"} , {"Dr Jackson", "20 Roman Close", "xxx@xxx.com"}}, {"Name", "Address", "Email"}),
     
    RemovedRows  =   Table.FromList(DataBase[Email], Splitter.SplitByNothing(), {"Email"}, null, ExtraValues.Error), 

    FilteredLeadWebsite= Table.RemoveMatchingRows(LeadWebsite,Table.ToRecords(RemovedRows)  ,"Email")
in
    FilteredLeadWebsite

In the query editor window, right click on the query list panel, right click and create a blank query.

Select the created query, open the edit window and paste the power query.

Check the steps one by one