Transform Multiple Columns into Replicated Rows

I'm looking to take data parsed out into individual fields/columns and 'flatten' my dataset to have a single column for all of the values.

The complexity I'm dealing with is that I want to retain all the other field values for each line item and repeat them for the new rows, with the exception of creating a unique tag in the Product field to identify or delineate the original source.

It seems from other posts maybe a Transform tool might help, but I can't figure out exactly how to set it up.

The example below shows my input and target output. (While I don't know if I need Total Sales in the result, and certainly don't want to replicate it so it's not double-counted, I left in both sides to help visualize how my data is set up)

Thanks.

Transform Columns to Rows Example Dataset.xlsx

Transformation

Accepted answers

DataNath

@Paul_Kelly how precious are you about the output format? You can get fairly close with a very simple workflow:

However, if it absolutely has to be like your image then this will require a bit more manipulation and thought. Also, I only needed to use the Data Cleanse tool to clean up your Excel file as you included the desired output etc - this shouldn't be necessary if you use the workflow so you can just right click > delete and connect around.

Transform fields into duplicate rows.yxmd

Felipe_Ribeir0

Hi @Paul_Kelly

One way of doing this

If the provided answer helped you to solve the problem/was correct, please accept it as a solution

Thanks.

Transform Multiple Columns into Replicated Rows.yxzp

All comments

DataNath

@Paul_Kelly how precious are you about the output format? You can get fairly close with a very simple workflow:

Transform fields into duplicate rows.yxmd

Felipe_Ribeir0

Hi @Paul_Kelly

One way of doing this

If the provided answer helped you to solve the problem/was correct, please accept it as a solution

Thanks.

Transform Multiple Columns into Replicated Rows.yxzp

Paul_Kelly

I can live with that. Thanks. So it is basically as simple as checking everything I want to keep as the Key Columns and then everything I want to compress into a single/new column get selected in the Data Columns section:

Not sure if I just didn't understand the terminology or was overthinking it, but this should give me what I need.

And if necessary, I can probably create a new field which combines what is in your Product & Name fields to compress into the layout I originally envisioned and communicated.

I appreciate the help.

Felipe_Ribeir0

Hi @Paul_Kelly

Please take a look at my response, i think that it has the exact format that you need.

Paul_Kelly

This looks great. Thanks for the effort in putting it all together.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703