Azure Data Factory - Derived column question

Adrian 181 Reputation points


I am trying to utilize Azure Data Factory for a transformation task.

I have a spread sheet with multiple sheets (15) on each of these sheets there is information about a specific product.

This is an example:


WHat I am after is creating a derived column in an entirely new sheet, in that excel file, that is a list of Product to Category informaton.

In the example above product '1000022' belongs to TTT0000202, TTT0000445 and TTT0000694. So this product will appear in the final list three times, for the three categories.

Any help as to how I should apporach this? There isn't much tutorials on datafactory that I could find, and not for this case anyway.


Azure Blob Storage
Azure Blob Storage
An Azure service that stores unstructured data in the cloud as blobs.
2,468 questions
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,442 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,687 questions
0 comments No comments
{count} votes

Accepted answer
  1. Adrian 181 Reputation points

    Hey, really appreciate you looking into this for me. I also know how to properly use the array() function now, which is very helpful.

    I have actually tried it in the same way but was still getting the same error, what actually ended up fixing it was removing the Space from my new column! I had the column name as 'Category Numbers', as soon as I changed it to 'CategoryNumbers' it has started to work. Not sure if this is a feature that was implemented consciously by Azure but oh well, learning everyday!


    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,126 Reputation points Microsoft Employee

    Hi @Adrian ,

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

    You need use derived column transformation to generate a new column to hold an array with three categories using array() function and then use flatten transformation to flatten that array to multiple rows.

    Please check below implementation to get better understanding.

    Step1: Source Transformation

    Step2: Derived column Transformation, use array() to generate array of all three categories as new column called "categories"

    Step3: Flatten transformation, to flatten "categories" array in to multiple rows

    Step4: Select Transformation, to select only "Product" & "categories" columns

    Once all transformations done you can use Sink transformation to load in to desired target.

    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 notification.
    0 comments No comments