How to compare 2 datasets in ADF dataflow and save the difference?

Question

I need to compare 2 tables and filter rows that are not the same and mark the column value in which they are different.

TABLE 1

User	Number	Type	Value
User1	1	A	value2
User1	2	A	value3
User1	1	B	value4
User1	2	B	value5
User1	1	C	value6
User1	2	C	Value7
User1	1	D	Value8
User1	2	D	Value9

TABLE 2

User	Number	Type	Value
User1	1	A	value2
User1	2	B	value3
User1	1	B	value4
User1	2	B	value5
User1	1	A	value6
User1	2	C	Value7
User1	1	D	Value8
User1	2	A	Value9

The final table should look like this:

FINAL TABLE

User	Number	Type	Value	Type Change
User1	2	B	value3	from A to B
User1	1	A	value6	from C to A
User1	2	A	Value9	from D to A

Answer 1

You can join TABLE1 and TABLE2 first, then use filter to get the rows which's type isn't same. Next create a DerivedColumn to generate Type Change column. Finally, use Select transformation to remain the columns you need.

Details:

1. Create Join transformation to TABLE1 and TABLE2.

2. Filter the rows which's type isn't same. Expression:source2@Type != source1@Type

3. Add Type Change column. Expression:concat('from ',source1@Type,' to ', source2@Type)

4. Use Select transformation to remain the columns you need.

Data Preview: