Azure Data Factory: Difference Between Two Record Values

Question

Looking for a way in Azure Data Factory to get the difference of the same column from the latest record to next-latest record.

For example, how to create the "Difference" column here:

ID Value Difference
04 230 25
03 205 0
02 205 5
01 200

Answer

Hello @bartnoble,

Welcome to Microsoft Q&A Platform.

We can achieve this using Dataflows. Please do check the below steps in detail:

Step1: Add two source transformations with your source data (because we need to do self-join in upcoming steps)

Step2: Perform Self-join using Join transformation

Step3: Select transformation to rename few columns as temp Columns

Step4: Filter transformation to take only rows which we are interested

Step5: Select transformation to remove TempID Column

Step6: Derived Column transformation to generate Difference column

Step7: Select transformation to remove TempValue Column

Step8: Sink transformation to load data in to desired sink resource

Hope this helps. Do let us know if you any further queries.

---------------------------------------------------------------------------

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

1 answer