Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Question Raised Previously:-
How do I get records from Table A that are not in Table B?
Answer:-
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.
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
User | Count |
---|---|
13 | |
5 | |
2 | |
1 | |
1 |
User | Count |
---|---|
24 | |
3 | |
3 | |
2 | |
2 |