Hi @ASK -
Your first question was regarding pivot/union and your second is looking to unpivot. The good news is that unpivot in data prep is “coming soon” see related question: How to unpivot columns in QuickSight? .
Here is a workaround that might get you by in the meantime.
Step 1 - Create a helper dataset “unpivot table”.
This is modified calendar table.
Step 2 - Create a calculated field in your dataset as a link.
Step 3 - Join your unpivot dataset helper with your dataset using the link field.
Step 4 - Create calculated fields to switch between the unpivoted columns
c_count
ifelse(Action='Creation' AND Date = {Creation Date},1,ifelse(Action='Deletion' AND Date = {Deletion Date},1,0))
Result:
