Mapping values from a dataset into a derived column for another dataset

HansJKiamzon 56 Reputation points
2020-11-18T18:39:34.153+00:00

Hi everyone. Would like to understand if there is a way in data flow to map a value from one dataset into a derived column for another dataset.

This is my use case: I have an excel workbook in which the first tab contains a field with the file's creation date. The rest of the tabs contain tabular data which I'll be creating separate datasets for, but this data does not contain a date indicating when it was created. The user has to refer to the first tab with the creation date. When creating the datasets for the tabular data, I'd like to add a column and populate each row with the excel file's created date.

Can this be done with a derived column, and if not, is there another approach within data flow to perform this logic?

Thank you and have a nice day.

Hans

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,996 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,833 questions
0 comments
Accepted answer
  1. Nasreen Akter 10,791 Reputation points
    2020-11-18T22:51:42.927+00:00

    Hi @HansJKiamzon ,

    Thank you for this question. Yes absolutely, this can be done by deriving a column in each DataSet with the same value. Steps would be:

    1. create a dataSet for the firstTab as well which contains the fileCreationDate
    2. in the DataFlow, derive an ID column with the same value for the FileDateDataSet as well as the TabularDataSet
    3. do a lookup as shown in the screenshots

    If this answer your query, please click “Accept Answer” and Up-Vote the answer. Thanks!

    40837-excelfiledatemapping1.jpg

    40931-excelfiledatemapping2.jpg

    2 people found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.