Split multiple columns value

Hi everyone,

I want to split multiple columns value. one evseId have one chargingNetwork and Country but powerType, Price and PriceType Could be one or two. In some columns more then 2 evseId. each evseId have at least one chargingNetwork and Country but powerType, Price and PriceType Could be one or two.

Below I attached sample data file.

Sample_Data.xlsx

Workflow

Tips and Tricks

Transformation

Accepted answers

All comments

binu_acs

@umarabdulsattar3275 have you tried the text-to-columns tool with the option split to rows,

umarabdulsattar3275

@binu_acs Yes I tried but I just split one column at once if I repeat for chargingNetwork It duplicate evseId and same if I apply text-to-columns tool again for powerType so then it again duplicate evseId and also chargingNetwork. at the end one evseId repeated multiple time which is not right.

As you can see in the screenshot. evseId (24487-1) just have one id, chargingNetwork and country but PowerType, Price and PriceType were two but when I split for each one just (24487-1) duplicate for 8 times