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.

transpose and regex problem.yxmd

Regex

Transformation

Accepted answers

jdunkerley79

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

transpose and regex problem.yxmd

All comments

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

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

transpose and regex problem.yxmd

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

transpose and regex problem.yxmd

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.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335