Parse delimited column in dataflow

Question

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.

Answer 1

Hi @Aditya Raj ,

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

Step 2: Source(employee data) and Sink(department data) transformations

Step 3: Join Transformation. Make sure to select Boardcast as Fixed and check Boardcast options.
Added Join condition dynamically by splitting parameter value.

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.

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.