Split the column values in dataflow in Azure Data factory

Question

I have few records that has following columns

Id, Person, housenumber
1, Mark, 101,102,
         103,104,
         105,106,
2,Alice, 107
3,Bob, 108,109,110

Note: So, we have 3 Id, where the first Id has housenumbers present in this format

I have created a dataflow, where I took the source file has source and added a derived transformation to split the housenumber split(housenumber, ', \n') then again created another derived column to get the index position of each house number

   house1 = split(housenumber, ', \n')[1]
   house2 = split(housenumber, ', \n')[2]..etc

the problem is I am not able to split the data accordingly. Also, some person can have more than 100 houses as well, in that case, creating a derived column 100 times will cause the problem as we need to update this derived transformation all the time.

Please help me to fix this kind of issues using dataflow in Azure data factory

Note: For simplicity purpose, I used Power BI query editor to show you the data. But we want the fix as per dataflow transformation that I can use further using pivoting then sink transformation

Expected output

Dataflow design-

Answer 1

As we could see you wants to have the array of string s to be split into different columns. Here is the approach where you can have the source and then passing it into a derived column which will then be flatten and then it will be copied to the sink.

• At first here is my source data in the preview:

• Then you can add a derived column with the housenumber to be split using the split(housenumber,’,’) as the expression to be provided via expression builder.

• Here, we go with flatten the data only using unroll by Housenumber

• Where the data to be previewed as :