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 / Hexadecimal representation?

 

Thank you,

 

Wouter

Need Help

3 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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