Azure data flow cross join

Question

I have one requirement that I need to achieve with Azure data flow.

enter image description here

As you can see in output that we can easily using cross and split function in sql query but same thing how can I achieve using data flow.

Answer 1

You can do this in ADF Data Flows.

1. From your flat file source, add a derived column
2. Create a new column called "countries".We'll take your multi-valued column and spit it into that array using this expression: split(country,',')
3. Use Flatten to denormalize into the output you asked for
4. Flatten: Set unroll to countries (I called it myarray in the screenshot) and take out the original comma-delimited field from your field mappings.