BinaryFormat.Record

D

Back to top

T

Back to top

#

Back to top

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

Understanding BinaryFormat.Record

The BinaryFormat.Record function is used to specify the format of a binary file. This function takes in a list of record fields and their corresponding data types. Each record field defines a piece of data within the binary file. The data types can be one of the following:

– Int8

– Int16

– Int32

– Int64

– UInt8

– UInt16

– UInt32

– UInt64

– Float

– Double

– Decimal

– DateTime

– TimeSpan

– Text

– Binary

Creating a BinaryFormat.Record

To create a BinaryFormat.Record, you will need to define the record fields and their data types. Here is an example:

BinaryFormat.Record([

[Field1 = Int16.Type],

[Field2 = Text.Type],

[Field3 = DateTime.Type]

])

This code creates a BinaryFormat.Record with 3 record fields: Field1, Field2, and Field3. Field1 is of type Int16, Field2 is of type Text, and Field3 is of type DateTime.

Reading Binary Files with BinaryFormat.Record

Once you have defined the format of a binary file using BinaryFormat.Record, you can use the Binary.Buffer and Binary.Decompress functions to read in the binary file. Here is an example:

let

Source = Binary.Buffer(Binary.Decompress(File.Contents(“C:file.bin”), Compression.GZip)),

Format = BinaryFormat.Record([

[Field1 = Int16.Type],

[Field2 = Text.Type],

[Field3 = DateTime.Type]

]),

Result = BinaryFormat.Record.ReadAll(Source, Format)

in

Result

This code reads in a binary file located at "C:file.bin" using the Binary.Buffer and Binary.Decompress functions. The BinaryFormat.Record function is then used to define the format of the binary file. Finally, BinaryFormat.Record.ReadAll is used to read in all records in the binary file.

Limitations of BinaryFormat.Record

While BinaryFormat.Record is useful for reading in large datasets that cannot be loaded into memory, there are some limitations to keep in mind. First, the format of the binary file must be known ahead of time. If the format changes, you will need to update your M code accordingly. Second, BinaryFormat.Record can only be used to read in binary files. If you need to read in other file formats, you will need to use different M functions.

The BinaryFormat.Record function is a powerful tool for reading in large datasets that cannot be loaded into memory. By defining the format of a binary file, you can use Binary.Buffer and Binary.Decompress to read in the file. While there are some limitations to keep in mind, BinaryFormat.Record is a valuable function to have in your Power Query toolbox.

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)