Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a two column table. One of the columns has Employee Id and the other has a list of all the benefits that the employee holds. I want to split by delimiter, a comma, but have the columns sort by the benefit type as well. Here is what the data looks like
Employee ID | Benefits |
01 | Benefit 1/Family Plan, Benefit 2/Employee Plan,....,....,...,Benefit 7 |
02 | Benefit 1/Employee Plan, Benefit 3, Benefit 7 |
When I seperate by delimeter I get
Employee ID | Benefits | Benefits_1 | Benefits_2 | Benefits_3 |
01 | Benefit 1/Family Plan | Benefit 2/Employee Plan | ... | ... |
02 | Benefit 1/Employee Plan | Benefit 3 | Benefit 7 |
I would like to end with this table:
Employee ID | Benefits | Benefits_1 | Benefits_2 | Benefits_3 |
01 | Benefit 1/Family Plan | Benefit 2/Employee Plan | ... | ... |
02 | Benefit 1/Employee Plan | Benefit 3 |
This way all of the smae type plans are in one column together and a blank field shows up if that employee does not have that plan.
Solved! Go to Solution.
I made a simplified dataset.
First, split the Benefits column by comma delimiter into rows
Split the resulting Benefits column into columns by forward slash delimiter to get...
I trimmed Benefits.1 and also replaced the null values in Benefits.2 with 'Has'
Then Pivot Column Benefits.1 using values from Benefits.2 without aggregating
To end up with...
Hope this gets you pointed in the right direction.
Proud to be a Super User! | |
I made a simplified dataset.
First, split the Benefits column by comma delimiter into rows
Split the resulting Benefits column into columns by forward slash delimiter to get...
I trimmed Benefits.1 and also replaced the null values in Benefits.2 with 'Has'
Then Pivot Column Benefits.1 using values from Benefits.2 without aggregating
To end up with...
Hope this gets you pointed in the right direction.
Proud to be a Super User! | |