Forum Discussion

Amie-Louise's avatar
Amie-Louise
New Member
9 years ago
Solved

Get Records from Table A that are not in Table B

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...
  • kaushikd's avatar
    kaushikd
    9 years ago

    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

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
9 years ago

Amie-Louise

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])))
Amie-Louise's avatar
Amie-Louise
New Member
9 years ago

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. 

  • kaushikd's avatar
    kaushikd
    Resolver II
    9 years ago

    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

    • Amie-Louise's avatar
      Amie-Louise
      New Member
      9 years ago

      Thank you so much for sharing the below. 

      All working perfectly now :)

       

       

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    Amie-Louise

    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