Azure Data Flow convert Hex to Integer

Question

Good morning everyone.

We are using data factory and data flow to transform and after consume some data in power bi.

There is an column called "time" in csv we are transforming, it's type is hexadecimal, for instance "6022D4D8". This value it's an hex representation of timestamp in seconds.

0x6022D4D8 = 1612895448 = 2021-02-09 18:30:48 (in seconds)

Directly, with the value in plain text, it's possible to get the desire result:

toTimestamp(seconds(toInteger(0x6022D4D8)))

But, using the column string "time" we can't do it.

We tried everything: toInteger('0x' + time), unhex(time).

Could you helps us?

Kind regards

Answer 1

Dataflow functionality is continuously improved.
You can use toInteger(hexValue, ‘%x’) to convert from hex to base 10 integer. toLong also allows the same. Make sure to trim 0x from the string using substring or right functions.

Answer 2

After tried everything and also tried to work on @MartinJaffer-MSFT solution, we were not able to make the conversion on data factory natively, so we created an Azure Function to do the job and insert it on Data Factory pipeline just before the data flow, converting every hex data to integer in C#.
That's not the best solution, but we can remove that when Microsoft create a function inside Data Flow.
Thank you anyway and best regards