Reformat Column Headers to Match to allow for an easier "Join" or "Union"

[nmarinie]

Hi all,

I'm trying to reformat the column headers from 2 excel sheets so that they show in the same date format.

1 file has the dates listed as "Feb_2020" and the other has "2020_02".  I realize I can fix these for this particular instance but want a formula or way to continually update and reformat as this flow will be run monthly with new data.

Any other suggestions to join the data is appreciated as well!

Thanks

[CharlieS · Accepted solution]

Hi @nmarinie 

If those are the only two formats, then you can use a Dynamic Rename tool in Formula mode with the following expression:

IF RegEx_Match([_CurrentField_],"\w{3}_\d{4}") THEN
DateTimeFormat(DateTimeParse([_CurrentField_],"%b_%Y"),"%Y_%m")
ELSE [_CurrentField_] ENDIF

This expression will determine if the field needs to be renamed by RegEx pattern matching, then if it does, uses the DateTime functions to parse the information that's there and reformat it into the desired output.

Check out the attached workflow for an example of this in action. 

[nmarinie]

Thank you!