Name in column changes but always in the same position in Excel file

[MikeFrancis1959]

I have a excel file that I use all the time but I am not the originator of it. There is a column that always changes the name and but it will always be in column 36. How can I tell Alteryx to use this field regardless of the name. 

Thanks,

Mike

[OllieClarke · Accepted solution]

Here's one way of doing this:

You read in the excel with 'first row contains data' ticked. This lets you select columns by position

You can then rename just the specified column (F3 for me, F36 for you) using a dynamic rename:

Once you've done that, we can rename all other columns from the first row with another dynamic rename:

Finally we use a select tool to set the data types to what they should be:

You can now reference the field by name ([Fixed]) in all subsequent tools

This approach means we don't need to pivot our data (which can be slow with larger sets) - you could use an autofield tool instead of the select, but I don't like using one in production workflows. 

Hope that helps,

Ollie

[MikeFrancis1959]

Thank you, I will go through this to understand the flow and then proceed to change my workflow!

Mike

[danilang · Accepted solution]

Hi @MikeFrancis1959 

Here's slightly different way to accomplish this while retaining your original field types and restoring the original variable field name if required

Use a field info tool to get all the field names and add a record id.  In the Formula tool change the variable field to a static name and keep the other names the same.  Use a Dynamic Rename to rename from the old names to new ones.  Apply your formulas.  If required, you can go back to the original fields names by using a second Dynamic Rename to go from new names back to old. 

Dan

[MikeFrancis1959]

Thanks, I will try this also!!!

Mike

[NabilTan]

I've just used this and it works great, Thank you. I had a similar issue with data being sent with 2 columns which kept changing column name based on the month we were in. This has fixed that. It is now set up so that the changing column names become static running a few dynamic renames!