Binary.Split

What is Binary.Split?

Binary.Split is a Power Query M function that allows you to split binary data into separate columns. Binary data is data that is represented in binary form, such as a sequence of ones and zeros. Binary data is commonly used in computer systems to represent non-textual data, such as images or sound files.

The Binary.Split function takes two arguments: the binary data to be split, and the delimiter that should be used to split the data. The delimiter is a binary value that is used to separate the data into separate columns.

How to Use Binary.Split

To use the Binary.Split function, you first need to create a query in Power Query. Once you have created a query, you can use the Binary.Split function to split the binary data into separate columns.

Here is an example of how to use the Binary.Split function in Power Query:

let

binaryData = Binary.FromText(“01010100 01100101 01110011 01110100 00100000 01100100 01100001 01110100 01100001”),

splitData = Binary.Split(binaryData, Binary.FromText(” “))

in

splitData

In this example, we are using the Binary.FromText function to convert a text string into binary data. We then use the Binary.Split function to split the binary data into separate columns using a space as the delimiter. The resulting output is a table with a single row and eight columns, each containing one byte of binary data.

The M Code Behind Binary.Split

The M code behind the Binary.Split function is relatively simple. Here is the M code that is used to define the Binary.Split function:

(binary as binary, delimiter as binary) as list =>

let

bytePositions = List.Positions(

binary,

(i) => i = delimiter,

Occurrence.Last

),

byteCount = List.Count(bytePositions) + 1,

byteList = List.Generate(

() => 0,

(i) => i < byteCount,

(i) => i + 1,

(i) => if i = 0 then

Binary.Start(binary, bytePositions{i} – 1)

else if i = byteCount – 1 then

Binary.End(binary, bytePositions{i – 1} + 1)

else

Binary.Range(binary, bytePositions{i – 1} + 1, bytePositions{i} – bytePositions{i – 1} – 1)

)

in

byteList

The Binary.Split function takes two arguments: binary and delimiter. The binary argument is the binary data that will be split, and the delimiter argument is the binary value that will be used to split the data.

The function begins by defining a variable called bytePositions. This variable is created using the List.Positions function, which returns a list of the positions where the delimiter occurs in the binary data. The Occurrence.Last argument tells the function to return the last occurrence of the delimiter.

Next, the function calculates the number of bytes that will be generated by the Binary.Split function. This is done by counting the number of delimiter positions returned by the List.Positions function and adding one.

Finally, the function generates a list of bytes using the List.Generate function. The first argument to List.Generate is a function that initializes the list generator. In this case, we are initializing the list generator with a value of zero.

The second argument to List.Generate is a function that determines whether to continue generating values. In this case, we are continuing to generate values until we have generated the specified number of bytes.

The third argument to List.Generate is a function that generates the next value in the list. In this case, we are generating values based on the byteCount variable that we calculated earlier.

The fourth argument to List.Generate is a function that returns the value that was generated by the previous function. In this case, we are using a conditional statement to determine which byte to generate based on the current index in the list generator.

Binary.Split is a powerful function in Power Query M that allows you to split binary data into separate columns. The M code behind the Binary.Split function is relatively simple, but it is also quite powerful. If you work with binary data in your job, learning how to use Binary.Split can be a valuable skill to have.

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

Upcoming Courses

Contact Us