Column functions not supported in configurations - MDF Expression Error

Kalidass Murugesan 96

I am building a pipeline with a Mapping Data Flow activity in synapse analytics. I am trying to create a derived column with an expression similar to below. However, it fails with the error "Column functions not supported in configurations".

array(toString(byNames(columnNames())))

I need to dynamically get the list of column names and get the values on the fly. Looks like it is not working when I use byNames() function along with columnNames() function. Is there any workaround for this?

PS: I cannot use columns() function because I need to reorder or filter some of the columns.

MarkKromer-MSFT 5,186 Reputation points Microsoft Employee

2021-09-15T22:46:05.303+00:00

Just columnNames() alone will give you the column names
Philip Lee 1 Reputation point

2021-09-16T07:31:36.333+00:00

I'm having the same issue.

Trying to generate a hash of all incoming columns using- columns_hash = md5(byNames(columnNames())) which throws the error above.

If I pass a parameter in to generate the hash as in - columns_hash = md5(byNames(split($columns,','))) it works fine.

Why, when both the function split() and columnNames() return an array of string, which is then passed to the byNames() function, does the second expression work but the first fail?
Mark Kromer MSFT 1,146 Reputation points

2021-09-16T08:21:46.433+00:00

Wouldn't you use just want to hash the column values instead? md5(columns())
Philip Lee 1 Reputation point

2021-09-16T09:31:51.527+00:00

Yep - that worked :) Stupidity error on my part doh!!!

Thanks
Kalidass Murugesan 96 Reputation points

2021-09-16T16:15:25.083+00:00

Thanks for your response. However, just the columnNames() function does not work in my case because I actually want the values of the columns. I want to get all the columns dynamically first and then reorder the columns before calculating the hash value. This is to make sure the hash value is correct even if the column orders are different in the source file.
Mark Kromer MSFT 1,146 Reputation points

2021-09-16T16:25:17.31+00:00

Reorder your columns with a Select transformation prior to the Derived Column. Then you can use just md5(columns()) to get the row hash of the column values using the column order you set.
Kalidass Murugesan 96 Reputation points

2021-09-16T16:31:55.967+00:00

Is there a way to reorder the columns with Select transformation if I do not know my columns at the design time.