Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Amie-Louise
New Member

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

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.

1 REPLY 1
Eric_Zhang
Microsoft Employee
Microsoft Employee

@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.

Capture.PNG

 

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

Capture.PNG

 

Check the steps one by one

 

Capture.PNG

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.