Azure Data factory to add additional rows in csv based on multiple values in a column

Question

I have an excel file in an Azure blob that I convert into csv file and perform some transformations also on that file. The excel file is a list of Product values for that day. So the columns are like Data, Product names, Value. The product names are a list of different products separated by space.

How do I denormalize this dataset where each product name is expanded into a separate row. Example this table below

Date | Products | Value
20-02-2019 | prod_1 prod_2 prod_3 | 43
20-02-2019 | prod_4 | 32
21-02-2019 | prod_1 prod_4 | 32
21-02-2019 | prod_2 | 40

should transform as

Date | Product | Values
20-02-2019 | prod_1 | 43
20-02-2019 | prod_2 | 43
20-02-2019 | prod_3 | 43
20-02-2019 | prod_4 | 32
21-02-2019 | prod_1 | 32
21-02-2019 | prod_4 | 32
21-02-2019 | prod_2 | 40

I've looked into mapping data flow and alter rows but they both need a database as a sink. I'm relatively new to ADF so wondering if there's a way to do such that the transformation is stored in a blob?

Is this actually an Excel file or pipe-delimited as per your sample data? — wBob
– wBob, Commented Feb 9, 2021 at 8:59
The actual file is an excel file. I'm just showing here how the data in the file will look like. — KanduriR
– KanduriR, Commented Feb 9, 2021 at 10:40

Himanshu Kumar Sinha · Accepted Answer · 2021-02-09 05:38:31Z

1

Adding the solution and double clicking on what @Mark Kromer MSFT said . I was able to achieve this by using the Derived column - I splited the Products column on space ' ' . This gave an array with the poduct names . I used the flatten activity to Unroll by products . and then I used the Filter activity ( in youe dataset you have a space between the the pipe and the product name ( eg ) | prod_1. The below animation will make the implementation more clear .

answered Feb 9, 2021 at 5:38

Himanshu Kumar Sinha

1,8182 gold badges9 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KanduriR Over a year ago

Oh this is extremely helpful. Thank you.

Mark Kromer MSFT · Accepted Answer · 2021-02-07 23:39:13Z

0

You can do this in ADF data flows and sink it to a blob folder. Alter Row is only needed if you are performing update/delete/upsert operations.

answered Feb 7, 2021 at 23:39

Mark Kromer MSFT

3,8681 gold badge12 silver badges11 bronze badges

Collectives™ on Stack Overflow

Azure Data factory to add additional rows in csv based on multiple values in a column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related