arkiboys avatar image
0 Votes"
arkiboys asked ShaikMaheer-MSFT commented

dataflow - multiple column patterns

Currently, the pipeline reads a config file to load table data from a and push to b (Copy activity inside foreach loop).
There is a dataflow after the copy activity in the loop where it uses column patterns to check for the name of the table being processed...
for example:
if table == "table1" && name == "GroupName"
$$ --> lower($$)

The column patter above is one pattern I have.
The fact is that there are a-lot of columns in a-lot of tables being loaded which must have some kind of different transformations applied to them.
I can continue adding column patterns here in the derived column transformation but I feel the list will increase alot and there will be alot of different column patterns added each time...
What are my options for such a scenario? I guess I am trying to see if there is a more generic way to do this kind of work?

Thank you

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @arkiboys ,

Just checking in to see if the below answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @arkiboys ,

Thank you for posting query in Microsoft Q&A Platform.

Column patterns allow to define pattern based conditions. You can define patterns to match columns based on name, data type, stream, origin, or position instead of requiring exact field names.

Below are two main scenarios were column patterns are used:

  • If incoming source fields change often such as the case of changing columns in text files or NoSQL databases. This scenario is known as schema drift.

  • If you wish to do a common operation on a large group of columns. For example, wanting to cast every column that has 'total' in its column name into a double.

Below is sample example of column pattern.

So, I guess column patterns are the right way to proceed in your case.

If your looking for saving the expressions of derived column also in to some file and dynamically pass that expression itself, then that is not possible. Expressions should be written in expression builder only. They cannot be dynamic. That means, when you pass your expression as value from a parameter then is going to be treated as normal text only. Not as expression.

Hope this will help. Please let us know if any further queries.

  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

image.png (178.1 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

so if there are 50 tables and in each of those tables there are several fields which require expressions to be applied to, then how do I apply those many expressions in the column pattern? It will be a huge list, right?

0 Votes 0 ·

Hi @arkiboys - Yes you are right its going to be a huge list. I think you save your data flow expression in some table or file and try to load them dynamically in to Dataflows.
Inside dataflows to interpret that string as actual expression you can make use of expr() function.
Please check below video also for reference.

Please let us know how it goes. Also, please consider accepting it as answer if its helpful.

0 Votes 0 ·

thank you for the message...
I am aware of the expr() function which is a good tip...
The youtube video shows how to apply one expression for the whole table. Whereas I want to find-out how to read each expression which is assigned to each field and then in dataflow apply them to the transformation.
My point is mostly to do with where to place that huge list? Is it only the column pattern where I have to focus on?
How about reading a .csv file with column names and reading different expressions for each of the field names and then applying them in dataflow? is that a better way? if so, how exactly?

0 Votes 0 ·
Show more comments