Implementing Custom Binary File Format Parsers in Power Query M: Reading Fixed-Width, Delimited, and Proprietary Byte Structures into Typed Tables

Introduction

You're handed a data feed from a legacy mainframe system. The file has no extension. Opening it in a text editor reveals a wall of apparent gibberish — some readable characters, some not, no obvious delimiters, no headers. Your stakeholder tells you this file is generated every night by a COBOL batch job and contains the previous day's transactions. Your job is to get it into a Power BI model.

This is not a hypothetical. Industrial systems, scientific instruments, financial clearinghouses, point-of-sale vendors, and embedded hardware manufacturers all produce binary or semi-binary file formats. Some are open standards (like DBF or BMP). Many are proprietary. Most were designed by engineers who never imagined that a self-service BI tool would need to read them. And yet here you are.

By the end of this lesson, you will be able to parse any byte-oriented file format inside Power Query M — fixed-width records, custom delimited structures, and proprietary binary layouts — into fully typed, analysis-ready tables. You'll understand how M's binary primitive functions work at the byte level, how to design reusable parser functions, and how to handle the edge cases that will absolutely show up in production.

What you'll learn:

• How Power Query's Binary type and byte-manipulation functions actually work under the hood
• How to read and interpret fixed-width record layouts, including EBCDIC-encoded mainframe files
• How to parse custom delimited binary formats where the delimiter is a byte sequence, not a text character
• How to decode proprietary file headers and use them to drive dynamic record parsing
• How to assemble parsed bytes into typed M values (integers, floats, dates, text) with full control over endianness and encoding
• How to structure your parsers as reusable, composable functions that can handle variably-sized records and embedded metadata

Prerequisites

This lesson targets expert Power Query users. Before working through it, you should be comfortable with the topics covered in M Language Fundamentals: Syntax, Types, and Expressions for Power Query and Writing Custom M Functions from Scratch in Power Query. You should also have read the foundational article on Working with Binary Data in Power Query M: Reading, Parsing, and Converting Bytes to Structured Tables, since we'll be building significantly beyond those foundations here. Some familiarity with how bytes, bit ordering, and character encodings work will make the early sections easier to absorb, but we'll explain what we need as we go.

---

How Power Query Represents Binary Data

Before writing a single parser, you need a clear mental model of what you're working with.

In M, binary data is represented by the binary type. When you load a file using File.Contents("path/to/file.bin"), you get back a binary value. This value is essentially a sequence of bytes — unsigned 8-bit integers ranging from 0 to 255. Everything else you do is interpreting those bytes according to a schema you define.

The key functions in M's binary manipulation toolkit are:

// Load a binary file
let
    RawBytes = File.Contents("C:\data\transactions.bin"),
    
    // Get the total byte count
    TotalBytes = Binary.Length(RawBytes),
    
    // Read a slice of bytes starting at offset 0, length 4
    FirstFourBytes = Binary.Range(RawBytes, 0, 4),
    
    // Convert a binary value to a list of byte values (numbers 0-255)
    ByteList = Binary.ToList(RawBytes),
    
    // Convert a list of byte values back to binary
    BackToBinary = Binary.FromList(ByteList),
    
    // Decompress binary data (useful for gzip-wrapped files)
    Decompressed = Binary.Decompress(RawBytes, Compression.GZip)
in
    TotalBytes

The critical operation is Binary.Range(binary, offset, count). This is your scalpel. Everything about parsing binary files comes down to knowing where in the byte stream each piece of data starts and how many bytes it occupies, then slicing that range and converting it to an M value.

For converting byte ranges to typed values, M gives you the BinaryFormat namespace, which is where the real power lives:

// Reading a 32-bit little-endian unsigned integer starting at offset 0
let
    RawBytes = File.Contents("C:\data\readings.bin"),
    
    // BinaryFormat functions return "reader" functions
    // that consume bytes and produce values
    UInt32Reader = BinaryFormat.UnsignedInteger32,
    
    // Apply the reader to the binary data
    FirstValue = BinaryFormat.UnsignedInteger32(RawBytes)
in
    FirstValue

The BinaryFormat namespace contains readers for every common scalar type:

Function	Bytes	Description
BinaryFormat.Byte	1	Unsigned 8-bit integer
BinaryFormat.SignedInteger8	1	Signed 8-bit integer
BinaryFormat.UnsignedInteger16	2	Unsigned 16-bit, little-endian
BinaryFormat.UnsignedInteger32	4	Unsigned 32-bit, little-endian
BinaryFormat.UnsignedInteger64	8	Unsigned 64-bit, little-endian
BinaryFormat.Single	4	IEEE 754 single-precision float
BinaryFormat.Double	8	IEEE 754 double-precision float
BinaryFormat.Text	variable	Text with specified encoding and length

---

Parsing Fixed-Width Records: The Mainframe Case

Fixed-width formats are the most common binary-adjacent format you'll encounter. Each record is the same number of bytes. Each field occupies a defined byte range within that record. There are no delimiters — structure is entirely positional.

Understanding the Record Layout

Imagine you've received a specification like this from the legacy system vendor:

Record layout: 80 bytes per record
Offset  Length  Type        Field Name
0       8       ASCII       Account Number
8       4       Binary      Transaction Amount (cents, big-endian int32)
12      8       ASCII       Transaction Date (YYYYMMDD)
20      2       Binary      Transaction Type Code (big-endian uint16)
22      50      ASCII       Description
72      8       Binary      Reserved (padding)

This is a concrete, parseable spec. Let's build a function that handles it.

Reading a Single Record

First, we'll write a function that takes a binary slice representing one record and returns a record value:

let
    ParseTransactionRecord = (recordBytes as binary) as record =>
    let
        // Account Number: bytes 0-7, ASCII text
        AccountNumber = Text.Trim(
            BinaryFormat.Text(8, TextEncoding.Ascii)(
                Binary.Range(recordBytes, 0, 8)
            )
        ),
        
        // Transaction Amount: bytes 8-11, big-endian signed 32-bit integer
        // M's built-in BinaryFormat.Integer32 is little-endian
        // For big-endian, we reverse the byte list first
        AmountBytes = Binary.ToList(Binary.Range(recordBytes, 8, 4)),
        AmountBytesBE = List.Reverse(AmountBytes),
        AmountRaw = BinaryFormat.SignedInteger32(Binary.FromList(AmountBytesBE)),
        // Convert cents to decimal dollars
        Amount = AmountRaw / 100,
        
        // Transaction Date: bytes 12-19, ASCII YYYYMMDD
        DateText = BinaryFormat.Text(8, TextEncoding.Ascii)(
            Binary.Range(recordBytes, 12, 8)
        ),
        TransactionDate = Date.FromText(
            Text.Format("#{0}-#{1}-#{2}", {
                Text.Start(DateText, 4),
                Text.Middle(DateText, 4, 2),
                Text.End(DateText, 2)
            })
        ),
        
        // Transaction Type: bytes 20-21, big-endian uint16
        TypeBytes = Binary.ToList(Binary.Range(recordBytes, 20, 2)),
        TypeCode = TypeBytes{0} * 256 + TypeBytes{1},
        
        // Description: bytes 22-71, ASCII text, trimmed
        Description = Text.Trim(
            BinaryFormat.Text(50, TextEncoding.Ascii)(
                Binary.Range(recordBytes, 22, 50)
            )
        )
    in
        [
            AccountNumber = AccountNumber,
            Amount = Amount,
            TransactionDate = TransactionDate,
            TypeCode = TypeCode,
            Description = Description
        ]
in
    ParseTransactionRecord

Notice the big-endian handling. M's BinaryFormat.SignedInteger32 reads little-endian by default. For big-endian integers — standard in network protocols and many mainframe formats — you reverse the byte list before reading. The pattern List.Reverse(Binary.ToList(bytes)) is something you'll use constantly in mainframe parsing.

Splitting the File into Records

Now we need to read the entire file and split it into 80-byte chunks:

let
    Source = File.Contents("C:\data\transactions.bin"),
    FileLength = Binary.Length(Source),
    RecordLength = 80,
    RecordCount = Number.IntegerDivide(FileLength, RecordLength),
    
    // Generate a list of record indices
    RecordIndices = List.Generate(
        () => 0,
        each _ < RecordCount,
        each _ + 1
    ),
    
    // Extract each record as a binary slice
    RecordBinaries = List.Transform(
        RecordIndices,
        each Binary.Range(Source, _ * RecordLength, RecordLength)
    ),
    
    // Parse each record
    ParsedRecords = List.Transform(RecordBinaries, ParseTransactionRecord),
    
    // Convert to table
    ResultTable = Table.FromRecords(ParsedRecords),
    
    // Apply correct types
    TypedTable = Table.TransformColumnTypes(ResultTable, {
        {"AccountNumber", type text},
        {"Amount", type number},
        {"TransactionDate", type date},
        {"TypeCode", Int64.Type},
        {"Description", type text}
    })
in
    TypedTable

This pattern — generate indices, extract ranges, parse each range — is the core loop for all fixed-width binary parsing. The Advanced M: Iterators, Accumulators, and Recursive Patterns article covers the mechanics of List.Generate in depth if you want to go deeper on the iteration model.

Handling EBCDIC-Encoded Mainframe Files

True mainframe exports often use EBCDIC encoding rather than ASCII or UTF-8. EBCDIC is a completely different character encoding where byte values map to characters in a different order than ASCII. M doesn't have a native EBCDIC decoder, but you can handle it.

The approach is to build a lookup table that maps EBCDIC byte values to their ASCII equivalents, then apply that mapping to each character byte before text conversion:

let
    // Partial EBCDIC-to-ASCII lookup table (IBM Code Page 037)
    // Full table has 256 entries; this is abbreviated for illustration
    EbcdicToAscii = [
        #64 = " ",   // space
        #75 = ".",
        #76 = "<",
        #77 = "(",
        #78 = "+",
        #80 = "&",
        // ... all 256 mappings needed in production
        #193 = "A", #194 = "B", #195 = "C", #196 = "D",
        #197 = "E", #198 = "F", #199 = "G", #200 = "H",
        #201 = "I", #209 = "J", #210 = "K", #211 = "L",
        #212 = "M", #213 = "N", #214 = "O", #215 = "P",
        #216 = "Q", #217 = "R", #226 = "S", #227 = "T",
        #228 = "U", #229 = "V", #230 = "W", #231 = "X",
        #232 = "Y", #233 = "Z"
    ],
    
    DecodeEbcdicByte = (byteVal as number) as text =>
        let
            Key = "#" & Text.From(byteVal),
            Decoded = Record.FieldOrDefault(EbcdicToAscii, Key, "?")
        in
            Decoded,
    
    DecodeEbcdicField = (fieldBytes as binary) as text =>
        let
            ByteList = Binary.ToList(fieldBytes),
            Chars = List.Transform(ByteList, DecodeEbcdicByte),
            Result = Text.Combine(Chars)
        in
            Text.Trim(Result)
in
    DecodeEbcdicField

Decoding Packed Decimal (COMP-3) Fields

COBOL COMP-3 (Binary Coded Decimal) encoding stores two decimal digits per byte, with the last nibble indicating sign. This appears constantly in mainframe data. Here's how to decode it:

let
    DecodePackedDecimal = (packedBytes as binary, impliedDecimalPlaces as number) as number =>
    let
        ByteList = Binary.ToList(packedBytes),
        
        // Extract all nibbles (4-bit half-bytes)
        AllNibbles = List.TransformMany(
            ByteList,
            each {Number.IntegerDivide(_, 16), Number.Mod(_, 16)},
            (byte, nibble) => nibble
        ),
        
        // Last nibble is the sign: C or F = positive, D = negative
        SignNibble = List.Last(AllNibbles),
        IsNegative = SignNibble = 13, // 0xD
        
        // All nibbles except the last are digit nibbles
        DigitNibbles = List.RemoveLastN(AllNibbles, 1),
        
        // Combine digits into a number
        AbsoluteValue = List.Accumulate(
            DigitNibbles,
            0,
            (state, digit) => state * 10 + digit
        ),
        
        // Apply implied decimal scaling
        ScaledValue = AbsoluteValue / Number.Power(10, impliedDecimalPlaces),
        
        Result = if IsNegative then -ScaledValue else ScaledValue
    in
        Result
in
    DecodePackedDecimal

---

Parsing Custom Delimited Binary Formats

Not all binary formats are fixed-width. Some use byte sequences as delimiters between records or fields. These are trickier because you can't simply slice by a constant offset — you have to search for delimiter positions.

The Byte-Search Pattern

Imagine a scientific instrument that emits readings separated by a two-byte sequence 0x0D 0x0A (CRLF) between records and 0x7C (pipe, ASCII |) between fields within a record. But crucially, one field contains binary measurement data that may itself contain 0x0D bytes — so you can't just split on CRLF naively.

The right approach is to build an index of all delimiter positions in the byte stream and then slice between them:

let
    FindByteSequence = (source as binary, pattern as list, startOffset as number) as number =>
    // Returns the offset of the first occurrence of pattern starting at startOffset
    // Returns -1 if not found
    let
        SourceList = Binary.ToList(source),
        SourceLength = List.Count(SourceList),
        PatternLength = List.Count(pattern),
        SearchLength = SourceLength - PatternLength,
        
        MatchOffsets = List.Select(
            List.Generate(
                () => startOffset,
                each _ <= SearchLength,
                each _ + 1
            ),
            (offset) =>
                List.AllTrue(
                    List.Transform(
                        List.Generate(() => 0, each _ < PatternLength, each _ + 1),
                        (i) => SourceList{offset + i} = pattern{i}
                    )
                )
        ),
        
        Result = if List.IsEmpty(MatchOffsets) 
                 then -1 
                 else List.First(MatchOffsets)
    in
        Result
in
    FindByteSequence

For smaller files, the approach works well. But let's look at a more efficient strategy using List.Positions on a byte list:

let
    // More efficient: convert entire file to byte list once, then work with indices
    Source = File.Contents("C:\data\instrument_readings.bin"),
    FullByteList = Binary.ToList(Source),
    TotalBytes = List.Count(FullByteList),
    
    // Find all positions of the record delimiter (0x0D, 0x0A)
    RecordDelimiter = {13, 10}, // CRLF
    
    // Build a list of record boundary positions
    // This is more efficient than calling FindByteSequence repeatedly
    AllOffsets = List.Generate(() => 0, each _ < TotalBytes - 1, each _ + 1),
    
    DelimiterPositions = List.Select(
        AllOffsets,
        (i) => FullByteList{i} = 13 and FullByteList{i + 1} = 10
    ),
    
    // Compute record start/end boundaries
    RecordStarts = List.Combine({{0}, List.Transform(DelimiterPositions, each _ + 2)}),
    RecordEnds = List.Combine({DelimiterPositions, {TotalBytes}}),
    
    RecordBoundaries = List.Zip({RecordStarts, RecordEnds}),
    
    // Extract each record as a binary slice
    RecordBinaries = List.Transform(
        RecordBoundaries,
        (bounds) => Binary.FromList(
            List.Range(FullByteList, bounds{0}, bounds{1} - bounds{0})
        )
    )
in
    RecordBinaries

Parsing Within-Record Fields

Once you have the record binaries, you apply a similar approach to split by the field delimiter, but now within each record's byte sequence:

let
    ParseInstrumentRecord = (recordBytes as binary) as record =>
    let
        ByteList = Binary.ToList(recordBytes),
        RecordLength = List.Count(ByteList),
        FieldDelimiter = 124, // 0x7C = pipe
        
        // Find pipe positions
        PipePositions = List.Select(
            List.Generate(() => 0, each _ < RecordLength, each _ + 1),
            (i) => ByteList{i} = FieldDelimiter
        ),
        
        // Build field boundaries
        FieldStarts = List.Combine({{0}, List.Transform(PipePositions, each _ + 1)}),
        FieldEnds = List.Combine({PipePositions, {RecordLength}}),
        
        FieldBoundaries = List.Zip({FieldStarts, FieldEnds}),
        
        // Extract each field
        Fields = List.Transform(
            FieldBoundaries,
            (bounds) => List.Range(ByteList, bounds{0}, bounds{1} - bounds{0})
        ),
        
        // Field 0: Sensor ID (ASCII text)
        SensorId = Text.FromBinary(Binary.FromList(Fields{0}), TextEncoding.Ascii),
        
        // Field 1: Timestamp (8-byte Unix timestamp, big-endian int64)
        TimestampBytes = List.Reverse(Fields{1}),
        TimestampUnix = List.Accumulate(
            TimestampBytes,
            0,
            (state, b) => state * 256 + b
        ),
        // Convert Unix epoch to datetime
        EpochBase = #datetime(1970, 1, 1, 0, 0, 0),
        Timestamp = EpochBase + #duration(0, 0, 0, TimestampUnix),
        
        // Field 2: Raw measurement (4-byte IEEE 754 single-precision float)
        MeasurementBytes = Fields{2},
        MeasurementBinary = Binary.FromList(MeasurementBytes),
        Measurement = BinaryFormat.Single(MeasurementBinary),
        
        // Field 3: Status flags (1 byte bitmask)
        StatusByte = Fields{3}{0},
        IsCalibrated = Number.BitwiseAnd(StatusByte, 1) = 1,
        HasWarning = Number.BitwiseAnd(StatusByte, 2) = 2,
        IsFault = Number.BitwiseAnd(StatusByte, 4) = 4
    in
        [
            SensorId = SensorId,
            Timestamp = Timestamp,
            Measurement = Measurement,
            IsCalibrated = IsCalibrated,
            HasWarning = HasWarning,
            IsFault = IsFault
        ]
in
    ParseInstrumentRecord

The bitmask operations — Number.BitwiseAnd(byte, mask) — are how you decode packed flag fields. This pattern appears everywhere in hardware and protocol data: a single byte can carry eight independent boolean flags, and you extract each one by ANDing with the appropriate power-of-two mask.

---

Parsing Proprietary Binary Formats with Dynamic Headers

The most sophisticated scenario is a file format where the header itself tells you how to parse the rest of the file. Think of formats like DBF, BMP, or vendor-specific export formats where the header contains schema information — field names, field types, field lengths — that determines how to read every subsequent record.

A Realistic Example: Custom Sensor Log Format

Let's say you're working with a proprietary format from an industrial monitoring vendor. The specification tells you:

• Bytes 0-3: Magic number 0x53 0x4C 0x4F 0x47 ("SLOG")
• Byte 4: Version number (uint8)
• Bytes 5-8: Number of records (little-endian uint32)
• Byte 9: Number of fields per record (uint8)
• Bytes 10 to (10 + fieldCount × 34 - 1): Field descriptor table, 34 bytes per field:
  • Bytes 0-15: Field name (null-padded ASCII)
  • Byte 16: Field type code (1=int32, 2=float32, 3=ascii_text, 4=datetime_unix32)
  • Bytes 17-20: Field length in bytes (uint32, little-endian)
  • Bytes 21-33: Reserved
• Remaining bytes: Record data, each record being exactly the sum of all field lengths

Parsing the Header

let
    ParseSlogFile = (filePath as text) as table =>
    let
        Source = File.Contents(filePath),
        ByteList = Binary.ToList(Source),
        TotalBytes = List.Count(ByteList),
        
        // ── Validate magic number ──────────────────────────────────────────
        Magic = List.Range(ByteList, 0, 4),
        ExpectedMagic = {83, 76, 79, 71}, // "SLOG"
        IsMagicValid = Magic = ExpectedMagic,
        MagicCheck = if not IsMagicValid 
                     then error Error.Record("Format.Invalid", "File is not a valid SLOG file", null)
                     else "valid",
        
        // ── Read header fields ────────────────────────────────────────────
        Version = ByteList{4},
        
        RecordCountBytes = List.Range(ByteList, 5, 4),
        RecordCount = List.Accumulate(
            List.Reverse(RecordCountBytes), // little-endian: reverse for accumulation
            0,
            (state, b) => state * 256 + b
        ),
        
        FieldCount = ByteList{9},
        
        // ── Parse field descriptor table ──────────────────────────────────
        FieldDescriptorSize = 34,
        FieldDescriptorsStart = 10,
        
        ParseFieldDescriptor = (index as number) as record =>
        let
            Offset = FieldDescriptorsStart + index * FieldDescriptorSize,
            NameBytes = List.Range(ByteList, Offset, 16),
            // Strip null padding
            NameBytesClean = List.Select(NameBytes, each _ <> 0),
            FieldName = Text.FromBinary(Binary.FromList(NameBytesClean), TextEncoding.Ascii),
            
            TypeCode = ByteList{Offset + 16},
            FieldTypeName = 
                if TypeCode = 1 then "int32"
                else if TypeCode = 2 then "float32"
                else if TypeCode = 3 then "text"
                else if TypeCode = 4 then "datetime_unix32"
                else "unknown",
            
            LengthBytes = List.Range(ByteList, Offset + 17, 4),
            FieldLength = List.Accumulate(
                List.Reverse(LengthBytes),
                0,
                (state, b) => state * 256 + b
            )
        in
            [
                FieldName = FieldName,
                TypeCode = TypeCode,
                FieldTypeName = FieldTypeName,
                FieldLength = FieldLength
            ],
        
        FieldDescriptors = List.Transform(
            List.Generate(() => 0, each _ < FieldCount, each _ + 1),
            ParseFieldDescriptor
        ),
        
        // Compute record start offset and record size
        RecordDataStart = FieldDescriptorsStart + FieldCount * FieldDescriptorSize,
        RecordSize = List.Sum(List.Transform(FieldDescriptors, each _[FieldLength])),
        
        // ── Parse all records ─────────────────────────────────────────────
        ParseField = (fieldBytes as list, typeCode as number) as any =>
            if typeCode = 1 then
                // int32 little-endian
                List.Accumulate(List.Reverse(fieldBytes), 0, (s, b) => s * 256 + b)
            else if typeCode = 2 then
                // float32
                BinaryFormat.Single(Binary.FromList(fieldBytes))
            else if typeCode = 3 then
                // ASCII text, null-trimmed
                Text.Trim(Text.FromBinary(
                    Binary.FromList(List.Select(fieldBytes, each _ <> 0)),
                    TextEncoding.Ascii
                ))
            else if typeCode = 4 then
                // Unix timestamp as 32-bit unsigned int, little-endian
                let
                    RawSeconds = List.Accumulate(
                        List.Reverse(fieldBytes), 0, (s, b) => s * 256 + b
                    ),
                    Epoch = #datetime(1970, 1, 1, 0, 0, 0)
                in
                    Epoch + #duration(0, 0, 0, RawSeconds)
            else null,
        
        ParseRecord = (recordIndex as number) as record =>
        let
            RecordStart = RecordDataStart + recordIndex * RecordSize,
            
            // Parse each field using its descriptor
            FieldValues = List.Accumulate(
                FieldDescriptors,
                [offset = RecordStart, values = {}],
                (state, descriptor) =>
                let
                    FieldBytes = List.Range(ByteList, state[offset], descriptor[FieldLength]),
                    FieldValue = ParseField(FieldBytes, descriptor[TypeCode]),
                    NewOffset = state[offset] + descriptor[FieldLength],
                    NewValues = List.Combine({state[values], {FieldValue}})
                in
                    [offset = NewOffset, values = NewValues]
            ),
            
            // Build a record from field names and values
            FieldNames = List.Transform(FieldDescriptors, each _[FieldName]),
            Result = Record.FromList(FieldValues[values], FieldNames)
        in
            Result,
        
        // Parse all records
        AllRecords = List.Transform(
            List.Generate(() => 0, each _ < RecordCount, each _ + 1),
            ParseRecord
        ),
        
        // Convert to table
        ResultTable = Table.FromRecords(AllRecords)
    in
        ResultTable
in
    ParseSlogFile

This is a complete, self-contained parser for a proprietary format. Notice how the field descriptor table drives the parsing — the same ParseRecord function works for any combination of field types because it reads the schema from the file header at runtime. This is the hallmark of a robust proprietary format parser.

You can learn more about the broader type system implications of dynamically-built records in Mastering M Language Metadata: Attaching, Reading, and Leveraging Type Annotations for Robust Data Pipelines in Power Query.

---

Using BinaryFormat.Record for Declarative Parsing

For formats where you know the schema upfront (not dynamically defined in the header), M's BinaryFormat.Record gives you a cleaner, more declarative alternative to manual byte slicing:

let
    // Define a record format declaratively
    // This is the preferred approach when the schema is known at authoring time
    SensorRecordFormat = BinaryFormat.Record([
        SensorId = BinaryFormat.UnsignedInteger32,
        Temperature = BinaryFormat.Single,
        Humidity = BinaryFormat.Single,
        StatusFlags = BinaryFormat.Byte,
        Padding = BinaryFormat.Binary(3)
    ]),
    
    Source = File.Contents("C:\data\sensors.bin"),
    
    // Parse a list of records
    // BinaryFormat.List takes a format and optional count
    AllRecordsFormat = BinaryFormat.List(SensorRecordFormat, 100), // 100 records
    
    ParsedList = AllRecordsFormat(Source),
    
    ResultTable = Table.FromRecords(ParsedList)
in
    ResultTable

BinaryFormat.Record is clean and readable, but it has limitations: all formats must be known at query-write time, it processes bytes sequentially (each field immediately follows the previous one), and it's awkward for big-endian integers since all the BinaryFormat integer types are little-endian. For big-endian fields within a BinaryFormat.Record, you'd need to read them as BinaryFormat.Binary(n) and then reverse-and-convert separately.

Handling Variable-Length Fields with BinaryFormat.Choice

BinaryFormat.Choice is one of M's most powerful binary parsing primitives. It lets you read a "discriminator" value first, then choose a different format based on that value:

let
    // A format where each record starts with a 1-byte type code,
    // and the rest of the record structure depends on that type code
    RecordFormat = BinaryFormat.Choice(
        BinaryFormat.Byte, // read type code first
        (typeCode) =>
            if typeCode = 1 then
                // Type 1: fixed 20-byte temperature record
                BinaryFormat.Record([
                    TypeCode = BinaryFormat.Byte,    // wait, we already consumed this...
                    // Actually use Transform to combine
                    Temperature = BinaryFormat.Single,
                    Location = BinaryFormat.Text(15, TextEncoding.Ascii)
                ])
            else if typeCode = 2 then
                // Type 2: 8-byte pressure record
                BinaryFormat.Record([
                    Pressure = BinaryFormat.Double
                ])
            else
                // Unknown type: skip 10 bytes
                BinaryFormat.Binary(10)
    )
in
    RecordFormat

The correct pattern when you need the discriminator in the output is to use BinaryFormat.Transform:

let
    RecordFormat = BinaryFormat.Transform(
        BinaryFormat.Choice(
            BinaryFormat.Byte,
            (typeCode) =>
                if typeCode = 1 then
                    BinaryFormat.Transform(
                        BinaryFormat.Record([
                            Temp = BinaryFormat.Single,
                            LocationRaw = BinaryFormat.Text(15, TextEncoding.Ascii)
                        ]),
                        (r) => r & [TypeCode = 1] // merge TypeCode into the record
                    )
                else
                    BinaryFormat.Transform(
                        BinaryFormat.Record([
                            Pressure = BinaryFormat.Double
                        ]),
                        (r) => r & [TypeCode = 2]
                    )
        ),
        (r) => r // passthrough outer transform; inner transforms did the work
    )
in
    RecordFormat

---

Performance Considerations for Large Binary Files

Power Query's M engine is not designed to be a high-throughput byte processor. Its lazy evaluation model (well-explained in Understanding M Language Query Evaluation: Lazy Evaluation, Dependency Graphs, and Step Ordering in Power Query) means that accessing individual elements of very large lists repeatedly can cause repeated re-evaluation. Here are the patterns that matter for performance:

Rule 1: Convert Binary to List Once

// BAD: Binary.Range is called for every field in every record
// internally re-reading from the source binary repeatedly
Amount = Binary.Range(source, recordOffset + 8, 4)

// GOOD: Convert to byte list once at the start, use List.Range everywhere
FullByteList = Binary.ToList(source), // do this once
Amount = List.Range(FullByteList, recordOffset + 8, 4)

Rule 2: Avoid Nested List.Generate Calls

Nested List.Generate inside another List.Generate — like generating record indices and then generating field indices inside the record parser — creates quadratic evaluation pressure. Use List.Accumulate with an offset state instead, as shown in the ParseRecord function above.

Rule 3: Use Table.Buffer After Assembly

After building the table from parsed records, call Table.Buffer to materialize the result and prevent re-parsing:

BufferedResult = Table.Buffer(Table.FromRecords(AllRecords))

This is especially important if the parsed table is referenced by multiple downstream queries. Without buffering, each downstream reference can trigger a re-parse of the entire binary file. The performance implications of this are covered in M Language Performance Patterns and Anti-Patterns: Optimize Power Query for Speed.

Rule 4: Keep File Loads Out of Functions

Don't call File.Contents inside a function that's called per-record. Load the file once at the top level and pass byte slices to your parser functions. M may re-evaluate function calls in certain contexts, and re-loading the file in a loop would be catastrophic.

---

Hands-On Exercise

Download or create a binary test file using Python with the following script (run this outside Power Query to generate test data):

import struct
import os

records = [
    ("ACC001  ", 15099, "20240315", 1, "Online purchase - Electronics      "),
    ("ACC002  ", -5500, "20240315", 2, "Refund - Returns Department        "),
    ("ACC001  ", 320000, "20240316", 3, "Wire transfer - Payroll            "),
    ("ACC003  ", 750,   "20240316", 1, "Coffee shop - Downtown Branch      "),
    ("ACC004  ", -200,  "20240316", 4, "ATM fee - International            "),
]

with open("transactions.bin", "wb") as f:
    for acct, amount, date, ttype, desc in records:
        f.write(acct.encode("ascii")[:8].ljust(8, b" "))
        f.write(struct.pack(">i", amount))       # big-endian int32
        f.write(date.encode("ascii")[:8])
        f.write(struct.pack(">H", ttype))        # big-endian uint16
        f.write(desc.encode("ascii")[:50].ljust(50, b" "))
        f.write(b"\x00" * 8)                     # padding

Then, in Power Query, implement the following:

1. Load transactions.bin using File.Contents
2. Implement the ParseTransactionRecord function shown earlier in this lesson
3. Split the file into 80-byte record chunks using List.Generate and Binary.Range
4. Parse all records and assemble them into a typed table
5. Add a calculated column that maps TypeCode values (1=Purchase, 2=Refund, 3=Transfer, 4=Fee) to descriptive text
6. Filter for only records where Amount > 0 (credits only)

The expected output should be a 3-row table with ACC001 (Electronics, $150.99), ACC001 (Wire Transfer, $3200.00), and ACC004 excluded. If your amounts come out wrong, check your big-endian byte reversal — a common first mistake is forgetting to reverse before accumulating.

---

Common Mistakes & Troubleshooting

Mistake 1: Getting byte offsets wrong by one

Off-by-one errors are extremely common in binary parsing. The symptom is garbled field values — your "Account Number" field contains the last byte of the previous record and seven bytes of the actual account number.

The fix: carefully check whether your offset convention is zero-based or one-based, and whether your field length includes or excludes a separator byte. Create a small test function that dumps a range to a hex string for visual inspection:

BytesToHex = (bytes as binary) as text =>
    Text.Combine(
        List.Transform(
            Binary.ToList(bytes),
            each Text.PadStart(Number.ToText(_, 16), 2, "0")
        ),
        " "
    )

Mistake 2: Forgetting that text fields are often space-padded, not null-terminated

ASCII text fields in fixed-width formats are almost always space-padded on the right. Binary text fields may be null-padded instead. Apply Text.Trim for space-padding and List.Select(bytes, each _ <> 0) for null-padding before converting to text. Getting this wrong leaves trailing garbage in your strings.

Mistake 3: Misidentifying endianness

If your numeric values are wildly wrong — orders of magnitude off, or negative when they shouldn't be — you've likely reversed the byte order. Verify by checking the spec. If the spec isn't clear, try both byte orders on a known value. For example, if you know a field should be the year 2024, and it comes out as 2099 or 1946, you have an endianness problem.

Mistake 4: Using BinaryFormat.Text with a fixed length on null-padded fields

BinaryFormat.Text reads exactly N bytes and interprets them all as text. If the field is null-padded, those null bytes become part of the string, which breaks downstream text comparisons and causes invisible whitespace issues. Always either use List.Select to strip nulls before calling Text.FromBinary, or apply a Text.TrimEnd with Character.FromNumber(0) after.

Mistake 5: Not handling file-size misalignment

If the file size isn't an exact multiple of your record length, something is wrong — either your record length spec is incorrect, there's a file header you haven't accounted for, or the file was corrupted in transit. Always validate this upfront:

Remainder = Number.Mod(Binary.Length(Source) - HeaderSize, RecordLength),
ValidationError = if Remainder <> 0 
    then error Error.Record(
        "Parse.Error", 
        Text.Format(
            "File size does not align with record length. Remainder: #{0} bytes", 
            {Remainder}
        ), 
        null
    ) 
    else null

Mistake 6: Float precision issues with BinaryFormat.Single

IEEE 754 single-precision floats have about 7 decimal digits of precision. If your sensor data is coming out as something like 23.450000762939453 instead of 23.45, you're seeing the precision limit of single-precision. Round to the appropriate number of decimal places: Number.Round(rawFloat, 2).

---

Summary & Next Steps

You now have a complete framework for parsing any binary file format in Power Query M. The core pattern is always the same: load bytes, locate structure boundaries (either by fixed offset or by searching for delimiter sequences), slice byte ranges, and convert each range to a typed M value using the appropriate conversion strategy. The sophistication comes from handling endianness correctly, decoding legacy encodings, and using the BinaryFormat namespace's declarative parsers for known-schema formats.

For production binary parsers, wrap your parsing logic into reusable functions following the patterns in Writing Custom M Functions from Scratch in Power Query and store shared lookup tables (like EBCDIC mappings and type code dictionaries) as shared parameter queries. Apply Table.Buffer after assembly to prevent re-parsing, and validate file structure before attempting to parse to surface data quality issues early.

The next frontier from here is streaming very large binary files using pagination patterns — worth exploring in the Streaming and Pagination Patterns in M: Handling Large APIs and Multi-Page Data Sources with Custom Iterators article. If your binary format is wrapped in a structured text envelope like JSON or XML before transmission, the techniques in Advanced JSON and XML Processing in Power Query M Language will help you peel that outer layer before handing the inner binary to the parsers you've built here.

Binary file parsing is one of the most technical capabilities in Power Query, and mastering it makes you genuinely irreplaceable in environments where everyone else has given up and said "we can't get that data in."