Split string column into multiple rows

AJ2020
AJ2020 Member
in Magic ETL

Hi,

I have a column that has anywhere from 0 to 25 notes in one cell. The delimiter for the cell is a line break after each note. I need to split the cell into a row for each note so that we can filter for just notes that just apply to a group like "Billing" or "Admin". How can this be accomplished?

image.png

We would like the result to look like this:

image.png

Thank you

Comments

  • Godiepi
    Godiepi Coach
    edited June 2022

    You will need to set up a Magic ETL like this :

    1) load your data into a Magic ETL

    image.png


    2) Split your Note column into multiple notes, In my example I use only 3 using the following configuration

    use a costum delimiter [\r\n] use regex

    image.png


    3) now you can unpivot to generate rows for each row by ItemID

    image.png


    4) the last step is just to drop the original column that contains the raw notes if you want to

    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'

Categories