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

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. 

Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft 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