Extract data from table1 which are different from Table2

[shwetakumari]

Scenario:

Table A : Having One Column 

Table B : Having Multiple Columns 

Table A (Col 1)

AAA

BBB

CCC

DDD

EEE

FFF

Table B (Col1 )

AAA

EEE

Output Expected  : Table A – Table B 

BBB

CCC

DDD

FFF

How to achieve the same. Kindly help

[Fowmy · Accepted solution]

@shwetakumari 

From the home tab, Merge Queries > Merge Queries as New > select

Merge Queries and select LEFT ANTI as join type. click OK and delete the last column

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[v-cazheng-msft · Accepted solution]

Hi, @shwetakumari 

You can use EXCEPT function to create a Calculated table to get the result you want.

Output Expected = EXCEPT(TableA,VALUES(TableB[Col1]))

The result looks like this:

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PC2790]

Hi @shwetakumari ,

You can do it in Power Query.

Follow the below steps:

1) Load the two tables as usual.

2) Go to 'Transform Data' to open Query Editor

3) Click on 'New Source' and select Blank Query.

4) Delete the existing code and replace it with the code provided below:

let
    differenceTA_= List.Difference(TableA[ID], TableB[ID]),
    TableA_ = Table.SelectRows(TableA, each List.Contains(differenceTA_, [ID]))
in
    TableA_

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Fowmy · Accepted solution]

@shwetakumari 

From the home tab, Merge Queries > Merge Queries as New > select

Merge Queries and select LEFT ANTI as join type. click OK and delete the last column

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn