1

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.

bonniethebest
  • 11
  • 1
  • You can try to use this expression to get index:`mapIf(keys($translation),#item == $$,#index)[1]` – Steve Johnson Apr 15 '21 at 09:21
  • Solid idea. I can't get the expression to work, though. Getting "Expression type could not be evaluated, correct the expression." Trying `values($translation)[mapIf(keys($translation),#item == $$,#index)[1]]` with a `toInteger()` wrapped on `#index` and the the whole `mapIf` without luck. Not sure where it fails. Will have to return to the problem. – bonniethebest Apr 16 '21 at 13:03

1 Answers1

0

I create a Simple Data Flow to test.

Data preview of source: enter image description here

Parameter: enter image description here

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: enter image description here

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]])

enter image description here

Steve Johnson
  • 8,057
  • 1
  • 6
  • 17
  • Trouble is I want to use a general rule on all tables, which might all have different number of columns. Example: I am taking a table from a "raw" db to a "processed" db, and I have 3 tables in Raw. They have 15, 25, and 48 columns. I want to keep each table that corresponds to an entry in the keys of the `$translation` array. Table 1 has 'abc', 'ghi' and 'mno'-columns, but table 2 and 3 only have 'abc' and 'ghi'. If I use your method, I need to know how many matching columns each table has, right? That's difficult to maintain as many of the tables have 100+ columns with 50+ matching keys. – bonniethebest Apr 19 '21 at 12:31
  • I want to keep each *column* that corresponds to an entry in the keys of the `$translation` array/map, sorry. – bonniethebest Apr 20 '21 at 07:04
  • @ I see. I haven't found any way to achieve this. It seems $$ can't work in find() and mapIf() method based on my test. If you have any solution, please post it. Thanks. – Steve Johnson Apr 20 '21 at 07:12