Alter Column: file to convert type String to Date

verytiredgirl

My current dataset has these columns:

Jan 2024 | Feb 2024 | Mar 2024 | Apr 2024 ………. and so on. And I'd want my table to just have 2 columns: Date and Point

So I used Unpivot tile, but I am unsure how to label my the new Date columns (number 4), so it will turn into a date format using Alter Column tile afterward. Do I need to add the date in front? Like 31/01/2024?

Manasi_Panov

Hi @verytiredgirl,

In this case, after performing the Dynamic Unpivot, you'll need to spend a bit more time creating a formula tile to convert 'FEB POINTS' to '01 Feb 2024', 'MARCH POINTS' to '01 Mar 2024', and so on. Then, change the format of the 'Date' column to Date using a Formula or Alter Column tile.

Manasi_Panov

Hello @verytiredgirl,

Why don't you try Dynamic Unpivot instead of Unpivot?

If your data initially includes columns for Jan 2024, Feb 2024, Mar 2024, and Apr 2024, start by Add Constant tile, then perform a dynamic unpivot. Here's an example:

Initial Dataset:

Magic ETL:

Add Constant tile:

Dynamic Unpivot tile:

Result:

Then you can remove the TEMP column and convert the Date column in any format you like.

verytiredgirl

I have 15 other columns that are not related to point, things like Customer_ID, city, email, etc and ultimately I want to pick a Customer and see their points overtime.

I have just tried the Dynamic Unpivot Tile, adding all other columns that are not points to "Select which columns will not be pivoted", but it didn't solve the Date issue

Manasi_Panov

Hi @verytiredgirl,

Could you please take a screenshot of your initial dataset columns?

If you have other columns, then Add Constant is not needed. Just add all the columns you don't want to unpivot here:

verytiredgirl

Hi, due to privacy reason, I can't share the screenshot, but that's what I did as commented above. I selected all the columns I don't want to unpivot into 1, 2 is Date, 3 is Point, the screenshot is the preview

Manasi_Panov

Hi @verytiredgirl,

In this case, after performing the Dynamic Unpivot, you'll need to spend a bit more time creating a formula tile to convert 'FEB POINTS' to '01 Feb 2024', 'MARCH POINTS' to '01 Mar 2024', and so on. Then, change the format of the 'Date' column to Date using a Formula or Alter Column tile.