Splitting single row into 2 based on a column

Jasdev

Hi.

I have a scenario for which I have been thinking from a lot time but couldn't figure out how it can be done. 

My input looks like

col1	col2	col3	col4	col5
a			1	2
b	3	4
c
d	5	6	7	8

My output should look like

col1	op2	op3
a	1	2
b	3	4
c
d	5	6
d	7	8

I can figure out a way where either of col2, col3 or col4 col5 are populated, i can use if else to get output. I am not understanding the scenario of splitting col1 where all col2,3,4,5 are populated so I have to split them in 2 different rows. Here, col2 and col3 and one set like cost and quantity for one scenario and col4 and col5 are cost and quantity of different scenario so theyll have values together or will be 0 together. Is there a way to generate the rows completely and split these 2 different scenarios? 

TomWelgemoed

Hi @Jasdev ,

You owe me half an hour of my life! 😉 Was more tricky than I thought.

Think the attached does it. We even accommodate good ol' Ram. 🙂

Think the main key was to split the column numbers from the pivoted columns and then to concatenate onto the same row the different values for the different columns. That allows you to identify the cases where there needs to be 2 rows or 1 row.

Will make more sense when you work through the workflow, so I attach that and an image. 

Best,
Tom

Creating new rows.yxzp