Azure Synapse Mapping Data Flow - Dynamic mapping of column names in derived columns

Question

In a Mapping Data Flow activity, I have a bunch of tables coming from an unprocessed area of a storage account, and I aim to select only some of these columns for the next more-processed-area. In selecting the columns, I need to translate the column names to something more intuitive and/or lowercase the name. I intend to do this using parameters so I only need to change it in one spot if I need to make adjustments.

I managed the "easy" part - whitelisting relevant column names and making these lower case. But suppose I want to rename the columns according to a dictionary where column "abc" becomes "def" and "ghi" becomes "jkl". I am trying to do this in a Derived Column Transformation using a column pattern. I've made a map parameter (which I'm not sure is correct syntax):

['abc'->'def', 'ghi' -> 'jkl']

I think I need to find the index of the matching key in the translation map and then replace it with the correct index in the values array, but it doesn't seem like there's an easy way to extract the index from the functions available at https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expression-functions. This is what I have so far, partially pseudo-code (index):

replace($$,find(keys($translation),#item == $$),values($translation)[*index*(keys($translation),#item == $$)])

I've been stuck on this for too long, so I was hoping someone could give me some ideas on how to proceed.

Any help would be much, much appreciated.

Answer 1

I create a Simple Data Flow to test.

Data preview of source:

Parameter:

Then I have tested serval expression in DerivedColumn transformation：

1.In column pattern, using the following expression replace($$,find(keys($translation),toString(#item) == $$),values($translation)[mapIf(keys($translation),toString(#item) == $$,#index)[1]]), this can't work. Through this expression mapIf(keys($translation), 1 == 1, concat($$, $$)), I found $$ in mapIf() function can't work(It returns abc and ghi, expected value is abcabc and ghighi). I'm not sure this is a bug or ADF team designs it like this.

2.Then I didn't use column pattern just add column to have a try:replace(col1,find(keys($translation),toString(#item) == col1),values($translation)[mapIf(keys($translation),toString(#item) == col1,#index)[1]]) and replace(col2,find(keys($translation),toString(#item) == col2),values($translation)[mapIf(keys($translation),toString(#item) == col2,#index)[1]])

It can get correct values:

Conclusion:

Don't use column pattern and just add column, then use this expression:replace(columnName,find(keys($translation),toString(#item) == columnName),values($translation)[mapIf(keys($translation),toString(#item) == columnName,#index)[1]])