Transform the headers to The required Format

[DataPirate26]

Hi,

I want to transform my Headers Input to the Desired Header format. 

Basically need to replace the "_" from the headers except for few of them which has the same header name only a no 2 is added after it with "_". 

example : Turlock 0621_2. Apart from this types rest all "_" needs to be removed. The sample Data with headers are attached below. Can someone help me out in this. 

[gautiergodard]

hello @DataPirate26 

See solution attached!

[DataPirate26]

@gautiergodard You missed out one point which I mentioned. That there are few exceptions in which I need the underscores. Example Turlock 0621_2. There are 3 4 cases in which a no 2 is added at the last. In those cases I need to keep the underscores. Can you check the desired headers Sample once. 

[DataPirate26]

@gautiergodard When the Headers are exact same but only at last the No 2 is added in that case i need to keep the underscore before the number 2. Apart from that all underscores to be removed

[gautiergodard · Accepted solution]

@DataPirate26 here you go!

[DataPirate26]

@gautiergodard This worked perfectly fine. Thanx a lot for your quick response. 

[binuacs · Accepted solution]

@DataPirate26 One way of doing this 

IIF(EndsWith([_CurrentField_], '_2'),Replace(REGEX_Replace([_CurrentField_], '(.*)(_.*)', '$1'),'_','-')+'_2',Replace([_CurrentField_],'_','-'))