I am having a below column called"Assigned To" in my dataset

I am trying to split this column using a delimiter in the derived column (split({Assigned To}, ";")) and it's converting into Array

When I tried to use Flatten to convert into multiple rows, I am getting the below error message

Not sure why this error coming? I believe some of the rows in my data have only one value example, Smith only name presents in the first row and the same goes for Jim in the fourth row. Is this error happening because of this one value in the array? Can anyone advise how to overcome this?

I need output like below

I can see the results preview in the derived flow

This is my Flatten flow

Getting this error

CodePudding user response：

I have reproed it with your sample data and it worked fine for me. Make sure to provide the correct column to the Unroll in Flatten settings.

When you are using flatten, make sure you do not have space in the column name

1. Source:

2. Derived Column:

Expression: split({Assigned To}, ';')

3. Flatten transformation:

Flatten data preview:

4. You can remove unwanted columns using Select transformation if needed.