new row for every column having a value

[Swati_Kejriwal]

Hi All, I am trying to perform transpose where i want a row for each record id where there is a value populated in any on of the columns against this records.

Sample Data:

record id	record type	record category	column 1	column 2	column 3	column 4
R1	RTA	RC1	C1	C2
R2	RTA	RC1		C2	C3	C4
R3	RTB	RC5			C3

Required Output:

record id	record type	record category	column 1
R1	RTA	RC1	C1
R1	RTA	RC1	C2
R2	RTA	RC1	C2
R2	RTA	RC1	C3
R2	RTA	RC1	C4
R3	RTB	RC5	C3

[apathetichell · Accepted solution]

transpose tool:

record id, record type and record category are key columns.

filter tool:

!isempty([value])

select tool:

rename [name] column 1

[atcodedog05 · Accepted solution]

Hi @Swati_Kejriwal 

Here is how you can do it.

Workflow:

1. Using transpose converting columns to rows.

2. Using data cleanse tool to clean values column.

3. Filtering of empty values.

4. Using filter tool to keep only required columns.

Hope this helps : )