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

Question

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

Answer 1

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!