Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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
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])))
Best Answer
Thank you
This was the best answer. Not sure why you would need to do an INTERSECT first (another answer). This worked for me. Thanks!
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.
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
Thank you so much for sharing the below.
All working perfectly now 🙂
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
4 | |
2 | |
2 | |
2 | |
1 |