Split columns to rows

Question

Hi there,

how do I split data in a column to rows at a delimiter?
I used split(columnname, '|') which gives an array
but I need the data in the array to be in different rows.

Eg:
I have below:

I need:

Thank you!

Answer 1

Hi @SPVat ,

Welcome to Microsoft Q&A forum and thanks for reaching out.

You can use a Derived column transformation to split the column value which will convert the column value to an array (Here is the expression used in below sample: split(ID, '|')), then followed by a Flatten transformation to Flatten the array and write the output to desired sink.

Please see below GIF where I have used a Delimited text file as source but can alter based on your source:

Hope this helps.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.