Data factory - find delta between records present in 2 excel files

Question

Hi, I have an use case - I have 2 excel files - structure wise same as below but number of rows are different. Lets assume, file ABC.xlsx has 1 row and file XYZ.xlsx has that same row and one extra row

I need to extract the delta i.e. the row in XYZ.xlsx file where Name is 'B'. I need to accomplish it using Azure Data Factory. Please guide me. Note: There is a possibility that Timestamp column might not be present. Regards, Partha

Answer 1

Hi, you can achieve this with a data flow:

1. Join the two dataset using Union
2. Use Aggregate to add a count column which holds the number of times a row exists. In the Aggregate, under "Group by", select all columns, then under "Aggregate" add a column "Count" with expression "count()"
3. Use Filter to only return the rows where Count equals 1. The expression is "Count==1"

I hope this helps you get started.