Azure Data Flow - Can we have Dynamic columns or change in projections for Unpiovt functionality

Question

The excel consist of 62 columns and 7 columns are fixed and rest of them have weeks as in year(week1 to week 52)

I have used a data flow task to unpivot the 53 columns into rows with 2 extra columns year and value. The problem is that I have the 52 week column names keep changing on every week data load and how to I handle this change in column names in data flow. For a single run it gives the exact output

Answer 1

What you'll want to do here is to implement late-binding of your schema, or what ADF refers to as "schema drift". Instead of setting a hardened "early binding" schema in your Source projection, leave the dataset schema and projection empty.

Next, add a Derived Column after your source and call it "Projection". This is where you'll build your projection using rules to account for your evolving schema.

Build out your canonical model with the column names for your entire year using byName('columnname'). That will tell ADF to look for the existence of the column in single quotes from your source data while also providing a schema that you can use to build out your pivot table.

If you need to cast the values, wrap byName() inside of a casting function, i.e. toString(), toDate(), etc.