Converting an Excel table into a useful Alteryx format - transpose and Regex problems

[chickenlicken]

I'm having difficulty figuring out how to correctly configure a table of data from Excel.

I have so far come up with a workflow that uses transpose and multi-row formula with Regex, but it is not working.

I feel there must be a simpler way... but I would also like to know why my attempt is not working.

[Thableaus]

Hi @chickenlicken

Could you please give us more details about your scenario?

What is your goal with this WF? How does your data need to look like at the end?

I see that you could have imported data on line 3 using Excel, instead of dynamic renaming your data twice.

Cheers,

[jdunkerley79 · Accepted solution]

Looking at your sample data I suggest:

I avoided using Regex to make it a little simple and used a join multiple for ease.

I think Data out is in right form

[chickenlicken]

Hi @Thableaus,

Thanks for your reply. 

I have been working a little more on this and have a workflow that now seems to work - the output at the end was basically what I am looking for, but I'm not 100% happy with the method of getting there as it is quite ugly and also relies on the automatic renaming of duplicate columns.  

The original Excel table has headers that I need to be able to reference for each data point, which was the reason for the dynamic rename...

[chickenlicken]

@jdunkerley79 Oh interesting, thank you!  I haven't used data cleanse before.... Your method definitely looks better than mine.  I will look more closely.   

[chickenlicken]

@jdunkerley79 thanks i really like how you used record id here to split out the fields that needed sorting before bringing them back into the rest of the workflow, rather than reconfiguring the entire thing like I was trying to do.

I love Alteryx, but learning it is tough!  Amazing the support in this community.