Split column data into another column

Question

Hi There,

I want to extract data from a column , which has key-value pairs separated by commas, into respective columns using Azure Data Factory data flow.

Example Data of Column containing key value pair:

"Application":"Test","Owner":"test@outlook.com","BusinessUnit":"Cloud"

I want to move the "BusinessUnit" key's value - here it is "Cloud" - to a column called "BU" , and it should look like this:

BU column now has - "Cloud" after moving.

Note - In the column containing key value pair the lenght is not fixed and its not guaranteed that we will find "BusinessUnit" at 3rd index everytime it can be at any index.

Thanks in advance for the help!

Much appreciated.

Answer 1

Hi Kothuri, Sravya,

Thanks for reaching out to Microsoft Q&A.

Check this...

1. Source Transformation: Start by reading your data into a data flow. Your source data contains key-value pairs separated by commas.
2. Derived Column Transformation:
   • Create a new column (let's call it newcol) that converts your existing "Info" column into an array of JSON objects. You can use the following expression:

split(replace(replace(replace(Info, '[', ''), ']', ''), '},{', '}|{'), '|')

This expression removes the square brackets and separates each JSON object using a pipe character (|).

3. Flatten Transformation:
   • Apply the flatten transformation to the newcol column. This will create separate rows for each JSON object within the array.
4. Parse Transformation:
   • Use the parse transformation to extract the key-value pairs from the JSON objects. Define the output column type as (key as string, value as string).
5. Sink Transformation:
   • In the sink settings, disable auto-mapping and manually map the parsed key-value pairs to the appropriate columns. For example, map the "BusinessUnit" key's value to the "BU" column.

By following these steps, you'll be able to extract the "BusinessUnit" key's value and move it to the "BU" column in your output data. This approach is flexible and can handle varying lengths and positions of the key-value pairs within your original column.

There's a similar question which has been answered previously as well...

https://learn.microsoft.com/en-us/answers/questions/545933/flatten-and-parsing-json-using-azure-data-flow.

https://community.fabric.microsoft.com/t5/Desktop/Extracting-a-list-of-key-value-pairs-into-multiple-columns/m-p/381676.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.