question

AdityaRaj-8613 avatar image
0 Votes"
AdityaRaj-8613 asked ShaikMaheer-MSFT commented

Parse delimited column in dataflow

I have a column c1 text datatype delimited by comma. I need to split this column and get all names from this column. These names are of column of other table. I need to apply join condition based on name of dynamic columns present in my column c1. How can i do it using dataflow in azure data factory. Column c1 data is dynamic i.e,there can be many names present there.

azure-data-factory
· 3
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.

Can you paste an example of the text column? Typically just using split() inside a Derived Column should work. An alternative is to use the Parse transformation if the column has format to it, i.e. headers + data.

0 Votes 0 ·

Text column data can be 'firstname,lastname'. In next row it can be 'firstname,lastname,id'
In next row, it can be different. Number of elements inside that text column is not fixed.

0 Votes 0 ·

Hi @AdityaRaj-8613 ,

Just checking in to see if the below answer provided by @ShaikMaheer-MSFT helped. If this answers your query, do click Accept Answer and Up-Vote 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

Hi @AdityaRaj-8613 ,

Thank you for posting your Query on Microsoft Q&A Platform.
Below detailed example explains how to dynamically add Joins based your delimited column values

Step 1: Parameter in Data flow holds value "depid,depname" and we should use these columns for join condition
108146-parameter.gif
Step 2: Source(employee data) and Sink(department data) transformations
108119-sourcesink.gif
Step 3: Join Transformation. Make sure to select Boardcast as Fixed and check Boardcast options.
Added Join condition dynamically by splitting parameter value.
108079-joinnew.gif
In your case if the column names inside your "uniqueColumns" parameter are not fixed, then you need to identify the maximum number of columns which you may get and then try to adjust all the "uniqueColumns" values to same number of columns. So that your Join Transformation will not break.

For example, In below example I am adjusting all rows to 3 columns size. Also, If any row has less number of columns than 3 then I am making it to dummy value 1.
108080-image.png

Hope this will help. Thank you.


  • Please accept an answer if correct. 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 email-notifications



parameter.gif (171.9 KiB)
sourcesink.gif (711.9 KiB)
joinnew.gif (627.9 KiB)
image.png (24.8 KiB)
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.