Split columns to rows

SPVat 21 Reputation points
2021-05-06T18:04:44.523+00:00

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:
94519-image.png

I need:
94478-image.png

Thank you!

Azure Cloud Services
Azure Cloud Services
An Azure platform as a service offer that is used to deploy web and cloud applications.
695 questions
Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
4,978 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,681 questions
Accepted answer
  1. KranthiPakala-MSFT 46,487 Reputation points Microsoft Employee
    2021-05-12T22:38:07.103+00:00

    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:

    96069-flattenpipedelimitedcolumnvalue.gif

    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.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.