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.
Solved! Go to 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.
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.
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.
Hi @Paul_Kelly
Please take a look at my response, i think that it has the exact format that you need.
This looks great. Thanks for the effort in putting it all together.