Compare Data from different data source and delete the difference

[Anonymous]

Hi I would like to use the Power Query to delete the difference between 2 different data sources.

One dada source is Share point list. The columns are RequestNumber,  Title, Status, Member ,ID and Comments.

RequestNumber	Title	Status	Member	ID	Comments
123	Test1	Closed	***	1	***
234	Test2	New	***	2	***
456	Test3	Updated	***	3	***

The other one is Excel.The columns are Number, Title, Date, RequestNumber,  Status.

Number	Title	Date	RequestNumber	Status
4	Test1		123	Closed
5	Test2		234	New
6	Test3		456	Updated
7	Test4		678	New

I would like to compare them and the key column is RequestNumber, then delete the different row from Excel.

Could you please kindly tell how to set the Power Query? Thank you very much!

[SandeepSugandh]

You can do merge query with Inner join and then delete the additional column.

Then remove the additional column.

[Anonymous]

HI @SandeepSugandh Thank you very much.

Could you please kindly tell which Join Kind I should select if I only want to get the difference rows?

[danextian · Accepted solution]

Hi @Anonymous ,

In Power Query, you can use merge to compare a column in one table with the column in another. The innerjoin type will keep only those rows that are found in the other table. Please see attached pbix for reference.

[Anonymous]

HI @danextian Thank you very much.

Could you please also kindly tell which Join Kind I should select if I only want to get the difference rows?

[danextian]

Use Left Anti. This should keep only the rows that are in the first table. If a row exists in the other table, then that will be filtered out.