Pivot Data in ETL

TMonty0319

I have dataset i am trying to reformat using a pivot table. I cant use the pivot chart because it only shows measures as values so i am trying to reorganize my data using pivot in etl. My data includes Facility, Date, and Task Completed (yes/no). Beow on left is how my data currently is and on right is how I want to set it up. Could someone assist? I am trying the pivot and unpivot tiles but having some trouble.

Current				To Be
Facility	Date	Task Cmpleted		Facility	1/1/2024	1/2/2024
Facility 1	1/1/2024	Yes		Facility 1	Yes	Yes
Facility 2	1/1/2024	Yes		Facility 2	Yes	Yes
Facility 3	1/1/2024	Yes		Facility 3	Yes	Yes
Facility 4	1/1/2024	Yes		Facility 4	Yes	Yes
Facility 5	1/1/2024	No		Facility 5	No	Yes
Facility 1	1/2/2024	Yes
Facility 2	1/2/2024	Yes
Facility 3	1/2/2024	Yes
Facility 4	1/2/2024	Yes
Facility 5	1/2/2024	Yes

MichelleH

@TMonty0319 I'm not aware of a systematic way to do this. However, I also don't recommend using Magic ETL to pivot date fields for a few reasons:

• This eliminates the date field, which means you can no longer use date-based filtering (This Week, YTD, etc.)
• Every new date would add another column, which you would then have to add to your card

Have you considered changing your "Task Cmpleted" column to have binary 1 and 0 values in the dataset's current structure so that you have a numeric value for a pivot table card?

MichelleH

@TMonty0319 I'm not aware of a systematic way to do this. However, I also don't recommend using Magic ETL to pivot date fields for a few reasons:

• This eliminates the date field, which means you can no longer use date-based filtering (This Week, YTD, etc.)
• Every new date would add another column, which you would then have to add to your card

Have you considered changing your "Task Cmpleted" column to have binary 1 and 0 values in the dataset's current structure so that you have a numeric value for a pivot table card?