Pivot Data in ETL

TMonty0319
TMonty0319 Member
in Connectors

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

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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?

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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?

Categories