locked
Converting hex to float (little endian) RRS feed

  • Question

  • Hi,

    I am having issues using Power BI. I have a column full of Hexadecimal strings and want to convert them to Float (Little Endian) so that I can make sense of the column's data. I've tried a range of different queries in the Advanced Editor but have had no success.

    An example of one of these fields is: c3f5ca42

    Does anyone have a solution for converting this column.

    Thanks in advance

    Monday, August 12, 2019 2:35 PM

Answers

  • BinaryFormat.ByteOrder(BinaryFormat.Single, ByteOrder.LittleEndian)(Binary.FromText("c3f5ca42", BinaryEncoding.Hex))
    • Proposed as answer by Curt Hagenlocher Monday, August 12, 2019 4:19 PM
    • Marked as answer by JonnyM2407 Wednesday, August 14, 2019 1:30 PM
    Monday, August 12, 2019 4:19 PM

All replies

  • BinaryFormat.ByteOrder(BinaryFormat.Single, ByteOrder.LittleEndian)(Binary.FromText("c3f5ca42", BinaryEncoding.Hex))
    • Proposed as answer by Curt Hagenlocher Monday, August 12, 2019 4:19 PM
    • Marked as answer by JonnyM2407 Wednesday, August 14, 2019 1:30 PM
    Monday, August 12, 2019 4:19 PM
  • Hi Curt,

    As a side question, regardless of the 32-character hex string in the expression:

    = BinaryFormat.Decimal(Binary.FromText("32 character hex", BinaryEncoding.Hex))

    an error is returned.

    No problems with BinaryFormat.Single for 8-character hex, or Binary.Format.Double for 16-character hex.

    What would be the reason for the error?

    Tuesday, August 13, 2019 5:25 PM
  • I don't know what exists in terms of standard decimal formats or where our format came from. It's always little-endian, has the mantissa first and then the scale, and the last byte is only for the sign bit.

    M> BinaryFormat.Decimal(Binary.FromList({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}))
    1
    M> BinaryFormat.Decimal(Binary.FromList({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 128}))
    -1
    M> BinaryFormat.Decimal(Binary.FromList({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 128}))
    -0.1

    Tuesday, August 13, 2019 9:18 PM
  • I don't know what exists in terms of standard decimal formats or where our format came from. It's always little-endian, has the mantissa first and then the scale, and the last byte is only for the sign bit.

    M> BinaryFormat.Decimal(Binary.FromList({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}))
    1
    M> BinaryFormat.Decimal(Binary.FromList({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 128}))
    -1
    M> BinaryFormat.Decimal(Binary.FromList({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 128}))
    -0.1

    Thanks! So I guess that Decimal is not applicable to Hex encoding.
    Tuesday, August 13, 2019 9:27 PM
  • M> Binary.ToText(Binary.FromList({1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 128}), BinaryEncoding.Hex)
    "01000000000000000000000000000180"
    M> BinaryFormat.Decimal(Binary.FromText("01000000000000000000000000000180", BinaryEncoding.Hex))
    -0.1
    Tuesday, August 13, 2019 9:53 PM