Modification of Excel data using data flow - Azure Synapse Analytics

Question

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.

Answer 1

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 or upvote 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