BinaryFormat.Single

D

Back to top

T

Back to top

#

Back to top

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

What is the BinaryFormat.Single function?

The BinaryFormat.Single function is used to convert a binary value into a single-precision floating-point number. The function takes a binary value as input and returns a floating-point number. The function is commonly used when working with data types that are not supported by Power Query, such as binary data.

Understanding the M code behind the BinaryFormat.Single function

The M code behind the BinaryFormat.Single function is fairly simple. The function takes a binary value as input and converts it into a single-precision floating-point number. Here is the M code for the BinaryFormat.Single function:

(BinaryValue as binary) =>

let

SingleValue = BinaryFormat.ByteOrder(BinaryValue, ByteOrder.LittleEndian),

Result = Number.FromBinary(SingleValue, 1),

in

Result

As you can see, the function takes a binary value as input and then performs two operations on it. First, it uses the BinaryFormat.ByteOrder function to specify the byte order of the binary value. The byte order can be either LittleEndian or BigEndian.

In LittleEndian byte order, the least significant byte of the binary value is stored first, while the most significant byte is stored last. In BigEndian byte order, the most significant byte is stored first, while the least significant byte is stored last.

After specifying the byte order, the function uses the Number.FromBinary function to convert the binary value into a floating-point number. The Number.FromBinary function takes two arguments: the binary value to convert and the number of bytes to use for the conversion. In this case, we are using a single byte to convert the binary value into a floating-point number.

Using the BinaryFormat.Single function in Power Query

Now that we understand the M code behind the BinaryFormat.Single function, let's see how we can use it in Power Query.

To use the BinaryFormat.Single function, we first need to have a binary value. Let's say we have a table that contains a column called BinaryData, which contains binary values. Here is an example of how we can use the BinaryFormat.Single function to convert the binary values into floating-point numbers:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjQwMjYxMlYyNDIwMjYxMFayUgoyMDM1ME5JNDXUNFUwNjZINzY2VjI0MDI2MTBYMjQyMDI2MTBaNkVWUlFqgQAA”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BinaryData = _t]),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“BinaryData”, type binary}}),

#”Added Custom” = Table.AddColumn(#”Changed Type”, “FloatData”, each BinaryFormat.Single([BinaryData])),

#”Changed Type1″ = Table.TransformColumnTypes(#”Added Custom”,{{“FloatData”, type number}})

in

#”Changed Type1″

As you can see, we first load the table into Power Query and then transform the BinaryData column into a binary data type. We then add a custom column called FloatData, which uses the BinaryFormat.Single function to convert the binary values into floating-point numbers.

Finally, we transform the FloatData column into a number data type. Once we have done this, we can load the data into Excel or Power BI and use it as needed.

The BinaryFormat.Single function is a powerful tool in Power Query that allows users to convert binary values into floating-point numbers. Understanding the M code behind this function can help users to use it more effectively and efficiently. By following the steps outlined in this article, users can use the BinaryFormat.Single function to transform their data and create powerful Excel and Power BI dashboards.

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)