Most Power Query guides stop at CSV and Excel. This lesson goes deeper — teaching you to parse raw binary files at the byte level, including fixed-width mainframe exports, custom delimited byte streams, and proprietary formats with dynamic headers, all inside native M code.
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:
Binary type and byte-manipulation functions actually work under the hoodThis 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.
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 |
Key insight:
BinaryFormatreaders are composable. They don't just read a single value — they can describe entire record structures.BinaryFormat.Record,BinaryFormat.List, andBinaryFormat.Choicelet you build complex parsers declaratively. This is the engine we'll use for all three format types.
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.
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.
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.
Warning: Don't confuse "big-endian" with "network byte order" conceptually — they are the same thing, but legacy mainframe specs often use the term "high byte first," which also means big-endian. If the spec says a field is "PIC 9(8) COMP" in COBOL, that's a packed decimal, which requires a completely different decode strategy discussed shortly.
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.
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
Tip: In production, store the full 256-entry EBCDIC lookup table in a separate query and reference it using the shared parameter pattern described in Cross-Query State Management and Shared Parameter Tables in Power Query M: Centralizing Configuration for Multi-Report Deployments. This avoids repeating a large literal in every parser function.
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
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.
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
Warning: The naive byte-search pattern shown above has O(n×m) complexity where n is file length and m is pattern length. For large files (tens of MB), this will be extremely slow in Power Query because M evaluates each list element lazily but still processes enormous lists. For files larger than a few MB with many records, consider pre-processing to a structured format using a Python or Power Automate step upstream, and only using Power Query for the resulting structured output.
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
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.
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.
Let's say you're working with a proprietary format from an industrial monitoring vendor. The specification tells you:
0x53 0x4C 0x4F 0x47 ("SLOG")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.
Key insight: The accumulator pattern in
ParseRecord— maintaining an[offset, values]state record as you iterate through fields — is the right approach for parsing sequentially-laid-out structures where each field's position depends on the total length of all preceding fields. This is a classic application ofList.Accumulatethat you'll use in every variable-layout parser you write.
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.
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.
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
Warning:
BinaryFormat.Choiceconsumes the discriminator byte and does not re-include it in the result. If you need the type code in your output record, you either have to read it again inside the chosen format (which will read the next byte, not the same one) or useBinaryFormat.Transformto attach it to the output. This is a common source of off-by-one errors when first usingBinaryFormat.Choice.
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
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.
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:
transactions.bin using File.ContentsParseTransactionRecord function shown earlier in this lessonList.Generate and Binary.RangeTypeCode values (1=Purchase, 2=Refund, 3=Transfer, 4=Fee) to descriptive textAmount > 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.
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).
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."