transform data in Azure data factory

Question

Gurusvasti 21

Does anyone know how to transform data in Azure data factory
I have a source file that contains data like this:

ABC Code-01
DEF
GHI
JKL Code-02
MNO

I need to make the data looks like this to the sink file:

ABC Code-01
DEF Code-01
GHI Code-01
JKL Code-02
MNO Code-02

Please hekp

Vaibhav Chaudhari 39,011 Reputation points Volunteer Moderator

2021-08-06T05:58:54.617+00:00

Data Flow inside ADF can be used. It has Derived Column task which can create such derived columns -

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-derived-column

----------

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav
MartinJaffer-MSFT 26,161 Reputation points

2021-08-06T20:49:55.96+00:00

anonymous userChaudhari , @MarkKromer-MSFT

This case looks particularly challenging. The ask is to populate a column based upon the earliest non-null value. This requires maintaining the order of the rows.

Perhaps first adding a surrogate key to keep the rows in order, then a window transformation to look back? I'm not certain this is a valid use of window transformation, but it is the closest idea I have. I am hoping to be able to leverage the previous row's value. The idea is that the first row must have a value. Then as we evaluate each row, write the new value so that the previous row always has a value.

If that doesn't work.... maybe the ask is not possible. Opinions?
Mark Kromer MSFT 1,146 Reputation points

2021-08-07T00:44:24.103+00:00

Yes, Window + Surrogate Key does the trick: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-script#fill-down

1 answer

Vaibhav Chaudhari 39,011 Reputation points Volunteer Moderator

2021-08-06T05:58:54.617+00:00

Data Flow inside ADF can be used. It has Derived Column task which can create such derived columns -

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-derived-column

----------

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav
MartinJaffer-MSFT 26,161 Reputation points

2021-08-06T20:49:55.96+00:00

anonymous userChaudhari , @MarkKromer-MSFT

This case looks particularly challenging. The ask is to populate a column based upon the earliest non-null value. This requires maintaining the order of the rows.

Perhaps first adding a surrogate key to keep the rows in order, then a window transformation to look back? I'm not certain this is a valid use of window transformation, but it is the closest idea I have. I am hoping to be able to leverage the previous row's value. The idea is that the first row must have a value. Then as we evaluate each row, write the new value so that the previous row always has a value.

If that doesn't work.... maybe the ask is not possible. Opinions?
Mark Kromer MSFT 1,146 Reputation points

2021-08-07T00:44:24.103+00:00

Yes, Window + Surrogate Key does the trick: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-script#fill-down

Answer 1

Mark Kromer MSFT 1,146

This is similar to the "fill-down" feature found in tools like Excel. We have a solution for this here in ADF data flows script snippets: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-script#fill-down

0 comments