Unpivot

verytiredgirl
verytiredgirl Member
in Magic ETL

Say my raw dataset looks like this:

image.png

And I would like to unpivot any column that has a month name is it, so in this example, it's 'Jan 2024 Total Pts Earned' and 'Jan 2024 Pts Redeemed', and the dataset would look like this (add a date column, total pts earned and total pts redeemed column):

image.png


How can I do this with Magic ETL? I've tried the 'Unpivot' Tile but you cannot add both 'Total pts earned' and 'total pts redeemed' along with the date column.

Tagged:

Best Answer

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hello @verytiredgirl,

    You can do it in several steps. Here is the example data:

    image.png

    First, make Dynamic unpivot:

    image.png

    Split the unpivoted column into two columns: one for dates and one for types:

    image.png

    Change the format of the 'Date' column into Date with the Alter Columns tile:

    image.png

    And finally, pivot the 'Type' column:

    image.png

    Here is the result:

    image.png

    I hope this answers your question ;)

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Answers

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hello @verytiredgirl,

    You can do it in several steps. Here is the example data:

    image.png

    First, make Dynamic unpivot:

    image.png

    Split the unpivoted column into two columns: one for dates and one for types:

    image.png

    Change the format of the 'Date' column into Date with the Alter Columns tile:

    image.png

    And finally, pivot the 'Type' column:

    image.png

    Here is the result:

    image.png

    I hope this answers your question ;)

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • verytiredgirl
    verytiredgirl Member

    Thank you, it worked!

Categories