BinaryFormat.Double

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function BinaryFormat.Double

What is a Double-Precision Floating-Point Number?

Before we dive into the M code behind the BinaryFormat.Double function, it’s important to understand what a double-precision floating-point number is. In computing, a floating-point number is a representation of a real number that can contain a fractional part. Double-precision floating-point numbers are a specific type of floating-point number that use twice as many bits to represent the number as a single-precision floating-point number.

Double-precision floating-point numbers have a higher precision than single-precision floating-point numbers, which makes them useful for applications where a high degree of accuracy is required. They are also used in scientific and engineering applications where small variations in the values can have a significant impact on the results.

The BinaryFormat.Double Function

The BinaryFormat.Double function is used to convert a binary value into a double-precision floating-point number. The syntax for the function is as follows:

BinaryFormat.Double(binary as binary, optional offset as number) as number

The function takes two parameters – the binary value that is being converted and an optional offset value. If an offset value is provided, the function will read the binary value starting from that offset, rather than from the beginning of the value.

The M Code Behind the BinaryFormat.Double Function

The M code behind the BinaryFormat.Double function is relatively simple. The function reads the binary value that is being converted and then uses a series of bitwise operations to convert the value into a double-precision floating-point number.

Here is the M code for the BinaryFormat.Double function:

let

BinaryFormat.Double = (binary as binary, optional offset as number) as number =>

let

offset = if offset <> null then offset else 0,

bytes = Binary.Buffer(Binary.Skip(binary, offset), 8),

sign = if Number.BitwiseAnd(bytes{0}, 128) = 128 then -1 else 1,

exponent = Number.BitwiseAnd(bytes{0}, 127) 16 + Number.BitwiseShiftRight(bytes{1}, 4),

fraction = Number.BitwiseAnd(bytes{1}, 15) 281474976710656 +

Number.BitwiseShiftLeft(bytes{2}, 48) +

Number.BitwiseShiftLeft(bytes{3}, 40) +

Number.BitwiseShiftLeft(bytes{4}, 32) +

Number.BitwiseShiftLeft(bytes{5}, 24) +

Number.BitwiseShiftLeft(bytes{6}, 16) +

Number.BitwiseShiftLeft(bytes{7}, 8),

value = sign (2.0 ^ (exponent – 1023)) (1.0 + fraction / 4503599627370496)

in

value

in

BinaryFormat.Double

The code first checks if an offset value has been provided and sets it to 0 if it hasn't. It then reads 8 bytes from the binary value, starting from the provided offset (or 0 if no offset is provided).

The code then uses a series of bitwise operations to extract the sign, exponent, and fraction components of the binary value. The sign component is determined by checking the most significant bit of the first byte – if it is 1, the number is negative, and if it is 0, the number is positive.

The exponent component is determined by extracting the next 7 bits of the first byte and combining them with the first 4 bits of the second byte. The fraction component is determined by extracting the last 4 bits of the second byte and combining them with the remaining 6 bytes of the binary value.

Finally, the code calculates the value of the double-precision floating-point number by combining the sign, exponent, and fraction components and using them to calculate the final value.

The BinaryFormat.Double function is a powerful tool for converting binary values into double-precision floating-point numbers within Power Query M. While the M code behind the function may seem complex, it is actually quite simple and easy to understand once broken down into its component parts. By using the BinaryFormat.Double function in your Power Query M code, you can easily convert binary values into a format that can be used in your data analysis and reporting.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)