Forum Discussion

shwetakumari's avatar
shwetakumari
New Member
5 years ago
Solved

Extract data from table1 which are different from Table2

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 –...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    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's avatar
    v-cazheng-msft
    5 years ago

    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's avatar
PC2790
Community Champion
5 years ago

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