How to rename a column based on it's column position... e.g, F21 of F24?

I have a dataset where I need to dynamically rename two columns based on their column position for changes made to reflect a new year. I am able to pull the field information and date into a stream where I use the Dynamic rename tool, but this isn't connected to the full dataset.

Thinking there should be an easier way to do this. Right now I am trying to transpose the columns to rows and then figure out a way to rename based on position this way. Below is a very simple visual of what I am needing to accomplish. Rename the name of the 4th column to a NewName.

Workflow

Datasets

Developer

Developer Tools

Input

Transformation

Accepted answers

ShankerV

Hi @SDJ57

Please find the output for FY 2022 and FY 2021.

IF contains([_CurrentField_],("FY "+left(datetimeadd(DateTimeNow(),-1,"year"),4)))
THEN "Next Year"
ELSEIF contains([_CurrentField_],("FY "+left(datetimeadd(DateTimeNow(),-2,"year"),4)))
THEN "Current Year"
ELSE [_CurrentField_]
ENDIF

Input was:

Many thanks

Shanker V

FY 2022 Column rename.yxmd

binu_acs

@SDJ57 Updated the workflow

Column_Rename_030223.yxmd

All comments

ShankerV

Hi @SDJ57

One way to do this!!!

Many thanks

Shanker V

Column rename.yxmd

SDJ57

Hi @ShankerV, thank you for your quick response! How could I do this is the name of "Column4" changes from year to year? I didn't explain that part well at all. So say, it starts as Column4 22, then the next year it is Column 423, but I need it to have the same name for both years. NewName.

So, it's the same file with the column name changing according to the year it is reporting in.

ShankerV

Hi @SDJ57

Just tweak my formula with contains() function.

It will work.

Many thanks

Shanker V

ShankerV

Hi @SDJ57

Using the contains function.

IF contains([_CurrentField_],"Column4")
THEN "NewName"
ELSE [_CurrentField_]
ENDIF

Input was:

Many thanks

Shanker V

Contains function Column rename.yxmd

ShankerV

Hi @SDJ57

Input changed for next year:

Output:

Many thanks

Shanker V

SDJ57

Thanks again @ShankerV! The name changes from year to year. Is there a way to build in a formula to calculate current year and previous year as part of the contains? For example, contains("Column +"Previous Year") or contains("Column +"Next Year")?

ShankerV

Hi @SDJ57

Yes, sure we can give.

Can you please give me an example like Year will be in 2 digits or 4 digits like that.

Many thanks

Shanker V

binu_acs

@SDJ57 One way of doing this

Column_Rename_030223.yxmd

SDJ57

Hi @binu_acs, I think that would get it, but I'm really not REGEX literate lol. There are 4 years with the same name structure but different years. So the exact format is "FY YYYY" descending down by year. So, the first one is in column 21 and the 2nd that I need is in column 24 the other 2 are in position 27 and 30.