Replacing select characters

[fardeen9983]

I have a list of fields with me obtained from parsing nested JSON data. Following is an example schema. The no of fields is dynamic in nature

yrseq	tax_lots_0_lot_price	tax_lots_0_lot_seq_no	tax_lots_1_lot_price	tax_lots_0_lot_seq_no
20190	210	12	446	45

I need to convert all the fields of type tax_lots_0_lot_price to tax_lots.0.lot_price. Basically replace the "_0_" to something like ".0.". I have transposed the table to make the columns as field value. Tried applying a REGEX_REPLACE method with a regex string of "_\d+_" The issue is when I want to replace the string I want it to retain the numeric value too. 

Your suggestions are most welcome. Thank you for your time

[messi007]

@fardeen9983,

You can do that with Dynamic Rename.

When you select Dynamic or unknown fields, when you have a new columns the formula will be applied 🙂

Please see below:

Attached the workflow,

If this solve your need, please mark it as solved. It will help others.

Regards,

[Luke_C · Accepted solution]

@fardeen9983 

Here's a more scalable approach, since you have JSON data I assume there will ultimately be a large number of columns that are 0,1,2, and so on. 

Similar to @messi007, I use the dynamic rename but first rename the fields in a way that any number value is maintained.