Binary.Range

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Binary.Range

What is the Binary.Range Function?

The Binary.Range function is used in Power Query to extract a range of bytes from a binary value. It takes three arguments:

1. The binary value to extract bytes from

2. The starting byte index

3. The number of bytes to extract

The function returns a binary value that consists of the specified range of bytes from the input binary value.

How to Use the Binary.Range Function

To use the Binary.Range function in Power Query, you need to follow these steps:

1. Open Power Query and create a new query.

2. Connect to the data source that contains the binary value you want to extract bytes from.

3. Add a custom column to the data by clicking on the “Add Column” tab and selecting “Custom Column”.

4. In the “Custom Column” dialog box, enter the following formula:

Replace "Binary Column" with the name of the column that contains the binary value, "Start Index" with the index of the first byte you want to extract, and "Number of Bytes" with the number of bytes you want to extract.

5. Click "OK" to create the custom column.

The Binary.Range function can be used in a variety of scenarios. For example, you can use it to extract a specific range of bytes from a binary file, such as an image or a PDF document. You can also use it to extract specific fields from a binary protocol message.

The M Code Behind the Binary.Range Function

To understand how the Binary.Range function works, let's take a look at the M code behind it. In Power Query, M is the formula language used to create queries. The M code for the Binary.Range function is:

(BinaryValue as binary, StartIndex as number, NumberOfBytes as number) as binary =>

let

EndIndex = StartIndex + NumberOfBytes,

ValueLength = List.Count(BinaryValue),

Range =

if StartIndex > ValueLength or EndIndex < 0 then

{ }

else if StartIndex < 0 then

Binary.Range(BinaryValue, 0, NumberOfBytes + StartIndex)

else if EndIndex > ValueLength then

Binary.Range(BinaryValue, StartIndex, ValueLength – StartIndex)

else

Binary.Range(BinaryValue, StartIndex, NumberOfBytes)

in

Range

This code defines the function and specifies the three arguments that it takes. The function first calculates the end index of the range by adding the starting index to the number of bytes. It then calculates the length of the binary value using the List.Count function.

The function then checks if the start index and end index are within the bounds of the binary value. If they are not, it returns an empty list. If the start index is less than zero, it uses the Binary.Range function to extract a range that starts from the beginning of the binary value and ends at the specified end index.

If the end index is greater than the length of the binary value, it uses the Binary.Range function to extract a range that starts at the specified start index and ends at the end of the binary value. If the start index and end index are within the bounds of the binary value, it uses the Binary.Range function to extract the specified range of bytes.

The Binary.Range function is a powerful tool in Power Query that allows you to extract a range of bytes from a binary value. Its M code is relatively simple and can be customized to fit a variety of scenarios. By understanding the M code behind the function, you can gain a deeper understanding of how it works and how it can be used to transform data in Power Query.

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)