- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

BRILLIANT! Thank you soooo much. I don't exactly understand what we did here but it works perfectly. 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I may have mis stated. I need the exact opposit. I need this
to look like this
Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
02-26-2025 07:40 AM | |||
05-06-2024 04:32 AM | |||
03-22-2023 09:45 AM | |||
04-21-2024 06:36 AM | |||
10-11-2024 01:05 PM |
User | Count |
---|---|
33 | |
18 | |
14 | |
11 | |
10 |