SQL timestamp reads as binary... How to convert?

[wodraeve]

Hi,

I'm reading from a SQL Server table using Power Query, and the Timestamp Data Type is returned as a binary type in PQ.

How can I convert this Binary type to a DateTime / Decimal / Hexadecimal representation?

Thank you,

Wouter

[Anonymous]

Hi wouter,

I face the same situation : did you solved the way to convert TimeStamps to text or date time ? 
The proposed solution is not solid and documented.

Didier

[v-yuta-msft]

Hi wodraeve,

You can call Text.FromBinary(binary as nullable binary, optional encoding as nullable number) to convert binary data to text data.

Then call Date.FromText(date as nullable text, optional culture as nullable text) to convert text data to date data.

Also, you can call Number.FromText(text as nullable text, optional culture as nullable text) to convert text to number.

Regarding number to hex, you should write a custom function with a hash table, please refer to this thread: https://community.powerbi.com/t5/Desktop/convert-from-hexadecimal-value-to-number/td-p/28435.

All the function above can be wrapped like Date.FromText(Text.FromBinary(......)).

Regards,

Jimmy Tao

[Anonymous]

Hi Yuta,

This does not help at all : the conversion to text does not work, especially for SQL Server Timestamp, which is seen as a binary by Power BI.
Please provide convinient solution