BinaryFormat.List

BinaryFormat.List

D

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

What is BinaryFormat.List?

BinaryFormat.List is a Power Query M function that takes a binary value and converts it into a list of values according to a specified format. The function is useful when you need to extract data from a binary file or a binary column in a table. The format parameter specifies how to interpret the binary data. The following format codes are supported:

– “b”: Byte (1 byte)

– “h”: Short integer (2 bytes)

– “i”: Integer (4 bytes)

– “q”: Long integer (8 bytes)

– “f”: Single-precision floating-point number (4 bytes)

– “d”: Double-precision floating-point number (8 bytes)

How to use BinaryFormat.List

The BinaryFormat.List function takes two parameters: the binary value and the format string. Here’s an example that shows how to use the function:


let

binary = Binary.FromText(“0102030405”),

list = BinaryFormat.List(binary, “b”)

in

list


In this example, we create a binary value by converting a text value "0102030405" to binary using the Binary.FromText function. Then we call the BinaryFormat.List function with the binary value and the format string "b", which tells the function to interpret the binary data as a sequence of bytes. The result is a list of five values: [1, 2, 3, 4, 5].

Here's another example that shows how to use the function with multiple format codes:


let

binary = Binary.FromText(“0102030405060708”),

list = BinaryFormat.List(binary, “bhhid”)

in

list


In this example, we create a binary value from the text value "0102030405060708". Then we call the BinaryFormat.List function with the binary value and the format string "bhhid", which tells the function to interpret the binary data as a sequence of one byte, two short integers, one integer, and one double-precision floating-point number. The result is a list of six values: [1, 513, 1027, 16909060, 578437695752307201].

Understanding the M code behind BinaryFormat.List

The M code behind BinaryFormat.List is quite complex, but we can break it down into three parts: parsing the format string, interpreting the binary data, and returning the list of values.

Parsing the format string

The first step in parsing the format string is to split it into individual format codes. We do this using the Text.ToList function, which converts the format string to a list of characters. Then we use a recursive function to parse the format codes. Here's an example:


let

format = “bhhid”,

formatCodes = Text.ToList(format),

parseFormat = (codelist) =>

if List.IsEmpty(codelist) then

{}

else

let

code = List.First(codelist),

rest = List.Skip(codelist, 1),

parsedRest = parseFormat(rest)

in

{code} & parsedRest,

parsedFormat = parseFormat(formatCodes)

in

parsedFormat


In this example, we define a variable format that contains the format string "bhhid". We then convert the format string to a list of characters using the Text.ToList function. We define a recursive function parseFormat that takes a list of characters and returns a list of format codes. The function works by taking the first character in the list (code) and recursively parsing the rest of the list (rest). The function returns a list that contains the code concatenated with the parsed rest of the list. Finally, we call the parseFormat function with the formatCodes list to get the parsed format: {"b", "h", "h", "i", "d"}.

Interpreting the binary data

The next step is to interpret the binary data according to the parsed format. We do this using a recursive function that takes the binary data, the parsed format, and a result list as parameters. Here's an example:


let

binary = Binary.FromText(“0102030405060708”),

format = “bhhid”,

parsedFormat = {“b”, “h”, “h”, “i”, “d”},

readValue = (binary, formatCode) =>

switch formatCode

case “b” => Binary.ByteOrder(binary, ByteOrder.BigEndian)

case “h” => Binary.Short(ByteOrder.BigEndian, binary)

case “i” => Binary.Integer(ByteOrder.BigEndian, binary)

case “q” => Binary.Long(ByteOrder.BigEndian, binary)

case “f” => Binary.Float(ByteOrder.BigEndian, binary)

case “d” => Binary.Double(ByteOrder.BigEndian, binary),

readValues = (binary, format, result) =>

if List.IsEmpty(format) then

result

else

let

formatCode = List.First(format),

restFormat = List.Skip(format, 1),

value = readValue(binary, formatCode),

restResult = List.Combine({result, {value}}),

parsedRest = readValues(binary, restFormat, restResult)

in

parsedRest,

list = readValues(binary, parsedFormat, {})

in

list


In this example, we define a binary value, a format string, and a parsed format list as before. We define a function readValue that takes a binary value and a format code and returns the interpreted value. The function uses the Binary functions to read the appropriate number of bytes from the binary value and convert them to the appropriate data type. Finally, we define a recursive function readValues that takes the binary value, the parsed format, and a result list as parameters. The function works by reading the value for the first format code using the readValue function, concatenating the value with the result list, and recursively parsing the rest of the format codes. The function returns the final result list.

Returning the list of values

The final step is to return the list of values. We have already defined the readValues function to return the list of values, so we simply call it with the binary value, the parsed format, and an empty list as the result.

In this article, we have explored the M code behind the Power Query M function BinaryFormat.List. We have seen how the function works and how to use it to extract data from binary files and columns. We have also looked at the M code behind the function and how it parses the format string, interprets the binary data, and returns the list of values. Hopefully, this article has given you a better understanding of the power and flexibility of the M language behind 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)

Copyright, G Com Solutions Ltd, 2024.
Tower Bridge Business Centre, 46-48 East Smithfield, London E1W 1AW
0800 998 9248   |   9:00 a.m. till 5:30 p.m.

Connect With Me: