Modification of Excel data using data flow - Azure Synapse Analytics

Kakehi Shunya (筧 隼弥) 201 Reputation points

I want to use Azure Synapse Analytics to modify an externally imported excel file, how can I achieve this using data flow?

There is an ID for each item above each item in the header.
How can I add this ID to the header?
Please see the attached image.
Any help would be appreciated.
Thank you.


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.
3,814 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
8,483 questions
{count} votes

1 answer

Sort by: Most helpful
  1. AnnuKumari-MSFT 28,076 Reputation points Microsoft Employee

    Hi @Kakehi Shunya (筧 隼弥) ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

    Since the above source data that you have shared is not having a proper schema, we have to look into various possibilities. You can try the following workaround, however, it is still not the optimal solution as I have hardcoded the ItemId values to achieve the requirement. Kindly look at the following steps we can take in mapping dataflow:

    1. Add the source transformation pointing your dataset to the .xls file and import schema.

    2. Use Conditional split transformation with the below conditions:
    source0: !isNull(Column_1) and source1:isNull(Column_1)



    3. Add sink transformation with .csv dataset to both the sources.

    Now, create another dataflow , having source as the first source0.csv file. Make sure to select first row as header.

    4. Add derived column transformation to add the required ItemID columns.



    5. Add select transformation to sort the columns in proper desired order. Add sink transformation to load this data in another .csv file.

    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