Transform Multiple Columns into Replicated Rows

[Paul_Kelly]

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.

[DataNath · Accepted solution]

@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.

[Felipe_Ribeir0 · Accepted solution]

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.

[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.