#binary

In this article, we will explore the M code behind the #binary function and how to use it to work with binary data in Power Query.

Understanding Binary Data

Before we dive into the #binary function, let’s first understand what binary data is. Binary data refers to any data that is stored in a binary format, consisting of sets of 0s and 1s. This includes images, audio, and video files, as well as other data types such as PDFs, ZIP files, and more.

When working with binary data, it can be useful to encode it in a specific format to make it easier to work with. Common encoding formats include Base64, Hexadecimal, and ASCII.

The #binary Function

The #binary function in Power Query is used to create a binary value from a given input. It takes a single argument, which can be any data type that can be converted to binary, such as a text string or a number.

For example, if we wanted to create a binary value from the text string “Hello World”, we could use the following M code:

This would create a binary value that represents the text string “Hello World”. We could then use this binary value in our Power Query transformations.

Reading Binary Data

Once we have binary data in Power Query, we may need to read or decode it to work with it. The #binary function also provides a way to read binary data using the following syntax:

This would read the base64-encoded data and return it as a binary value that we could use in our transformations.

Working with Binary Data

Now that we understand how to create and read binary data in Power Query, let’s explore some of the ways we can work with it.

Extracting Binary Data from Files

One use case for the #binary function is to extract binary data from files, such as images or audio files. To do this, we can use the File.Contents function to read the file contents, and then use the #binary function to create a binary value.

For example, if we wanted to extract the binary data from an image file named “my-image.png”, we could use the following M code:

This would create a binary value that represents the image file.

Converting Binary Data to Text

Another use case for the #binary function is to convert binary data to a text format. This can be useful when working with data formats such as Base64 or Hexadecimal.

To convert binary data to a text format, we can use the Text.FromBinary function. For example, if we had a binary value that represented a Base64-encoded string, we could use the following M code to convert it to text:

This would convert the binary value to a text string that represents the Base64-encoded data.

Manipulating Binary Data

We can also manipulate binary data using the standard Power Query functions and operators. For example, we could use the Text.Replace function to replace specific values in a binary value.

This would replace all instances of “find” in the binary value with “replace”.

The #binary function in Power Query provides a powerful way to work with binary data in Excel and Power BI. By understanding the M code behind the #binary function, we can create, read, and manipulate binary data in our Power Query transformations.

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

Upcoming Courses

Contact Us