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
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
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.
Furthermore, is there a way to apply this per group?
For example:
Name Value Difference
ABC 230 25
ABC 205 0
DEF 505 85
DEF 420
GHI 150 50
GHI 100
Hello @bartnoble,
Thank you for appreciating above solution.
Try to Split the groups in to different stream sets using conditional split transformation. And, on each stream set apply the same trick shared in above solution.
Check below link to know more details about conditional split transformation
https://docs.microsoft.com/en-us/azure/data-factory/data-flow-conditional-split
Hope this helps. Do let us know if you any further queries.
5 people are following this question.