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

[Amie-Louise]

Question Raised Previously:-

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

Answer:-

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)

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.

[Eric_Zhang]

@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