Forum Discussion
Pivot Challenge
I'm hoping someone can steer me in the right direction. I have a table with a list of employees and and calculated benefits for their weekly check. I need to be able to upload this data but I'm not sure how I can perform this transformation. The original table lists a row for every benefit but I need to pivot this so I only get one line per employee. To make it more challenging, not every employee has the same number of benefits so one one employee the first benefit might be Annuity but for the next the first may be INS. The transposition below is the closest I've gotten. I need the first row to be column 1. Any help would be GREATLY appreciated.
Hi AskGenie ,
My apology for not paying enough attention.
Starting point: (Based on the file I attached earlier) Step: Changed Type
We need two steps to achieve the outcome:
Step1: GroupBy
Once the GroupBy step generated, go to the formula bar replace the highlighted code below (Image A) with highlighted in Image B.
Image A
Image B
The next step is add a custom column with code below:
The final step is to combine all the tables by adding a custom step (blue circle below) and add the highlighted code:
Regards
KT
6 Replies
Hi AskGenie ,
There are a few ways to achieve the outcome.
One of the way is:
- Unpivot all the columns that forming the two column you want (i.e. PEN, $$ COP $$ etc),
- add index column,
- perform a lookup function with the help of the index column to get the previous row for the value or the name (i.e. PEN / COP), and this will set the benefits and it value next to each other,
- filter out the unnecessary rows.
Regards
KT
Thank you for your response, however, forgive me but I don't understand. should I implement your suggestion starting from from where I left off (employee code at top) or from my original table? What would a lookup formula for this look like? Thank you.
Hi AskGenie ,
My apology for not paying enough attention.
Starting point: (Based on the file I attached earlier) Step: Changed Type
We need two steps to achieve the outcome:
Step1: GroupBy
Once the GroupBy step generated, go to the formula bar replace the highlighted code below (Image A) with highlighted in Image B.
Image A
Image B
The next step is add a custom column with code below:
The final step is to combine all the tables by adding a custom step (blue circle below) and add the highlighted code:
Regards
KT
I may have mis stated. I need the exact opposit. I need this
to look like this
Thank you.
BRILLIANT! Thank you soooo much. I don't exactly understand what we did here but it works perfectly. 🙂
