ADF Unpivot Dynamically With New Column

Question

There is an Excel worksheet that I wanted to unpivot all the columns after "Currency Code" into rows, the number of columns need to be unpivot might vary, new columns might be added after "NetIUSD". Is there a way to dynamically unpivot this worksheet with unknown columns?

It worked when I projected all the fields and define the datatype for all the numerical fields as "double" and set the unpivot column data type as "double" as well. However, the issue is there might be additional columns added to the source file, which I won't be able to define the datatype ahead, in this case, if the new column has different data type other than "double", it will throw an error that the new column is not of the same unpivot datatype.

Answer 1

I tried to repro this in Dataflow with sample input details.

• Take the unpivot transformation and in unpivot settings do the following.

Ungroup by:  Code, Currency_code
Unpivot column: Currency
Unpivoted Columns: Column arrangement: Normal
                   Column name: Amount
                   Type: string

Data Preview

All columns other than mentioned in ungroup by can be dynamically unpivoted even if you add additional fields.

Answer 2

I confirm an Aswin answer. Got the same issue: failed dataflow with dynamically new columns. The reason was in datatype of unpivoted columns. Changed that to string and all goes smoothly. Imported projection does not affect this case i`ve tried both with imported and manually coded, both works with "string" datatype.