Forum Discussion

wodraeve's avatar
wodraeve
Frequent Visitor
8 years ago

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

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 / Hexadec...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Community Support
8 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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