Duplicating rows based on a condition

Ok so there probably a few ways to do this but hoping to see what the community thinks. I’m receiving an xlsx file where I need to duplicate the rows based on a condition (is the only way I can think).

example table looks like this:
customerid, totalcode, code1, code2, code3, code4, code5, code 6
1 A
2 B C
3 D
4 E F G H I J
5 K

So i’m trying to load each one of the multiple codes into its own row.

in the end i want the table to look like: codecount would containe the code number that has a value
customerid, totalcode, code1, code2, code3, code4, code5, code 6, codecount
1 A
2 B 1
3 C 2
3 D 3
4 E 1
4 F 2
4 G 3
4 H 4
4 I 5
4 J 6
5 K

So basically i need to remove that row, and replace it with a codecount (which tells me which column it was in) and what letter associated to it.

I tried look at repeat rows but there isn’t any documentation there. I looked over do some type of if statement to add the count. ultimately need one column with the count number of where it was so i can upload into my sql dbase table.

thoughts?

thanks
flowy

Adding a snippit of how i wanted it since it formatted my chart.

Hi @flowy,

Unpivoting, calculation, and re-pivoting seems to have done the trick. See the example below:

re-pivoting.morph (4.4 KB)

1 Like

Dmitry! Thank you! You never disappoint and looks like it will work for what I need. Going to implement this now in my program and will let you know if I have any questions! I didn’t realize how powerful the unpivot and pivoting was!

Flowy

Dmitry!

This worked fantatic!!!

Thank you
Flo

You’re welcome!