Working with Binary Data and File Formats in Power Query M: Reading, Parsing, and Converting Bytes to Structured Tables

Introduction

You're staring at a folder full of .bin files that a legacy manufacturing system dumps every hour. Or maybe you've got a third-party API that returns raw bytes instead of clean JSON. Or perhaps you need to read a fixed-width binary log format that Excel has never heard of. The Power Query UI can't help you here — there's no "From Binary Log" button. But the M language underneath Power Query absolutely can.

Most Power Query users never venture past the GUI. They connect to a CSV, click a few column headers, and call it a day. That's fine — until it isn't. When you hit a data source that doesn't come pre-packaged as a neat table, you need to understand what's happening at the byte level. This lesson is about exactly that: taking raw binary data, understanding how it's structured, and converting it into something you can actually analyze.

By the end of this lesson, you'll be able to read binary files from disk, slice and interpret their bytes using M's binary functions, decode values from raw byte sequences, and build a complete function that converts a custom binary format into a structured table. You'll also understand enough about how Power Query handles binary values internally that you can adapt these techniques to whatever unusual format you encounter next.

What you'll learn:

• What binary data actually is and how Power Query represents it
• How to read a file as raw bytes using File.Contents and Binary.Buffer
• How to navigate binary content using Binary.Range, BinaryFormat.*, and related functions
• How to extract typed values (integers, text, dates) from byte positions
• How to parse a realistic structured binary format into a proper M table

---

Prerequisites

• You should be comfortable writing basic M expressions in the Advanced Editor
• You understand what a let...in block is and how each works
• You've used List.Generate or similar list functions before (helpful but not required)
• You do NOT need to know anything about binary data beforehand — we'll start from zero

---

What Binary Data Actually Is (And Why It's Not Scary)

Every file on your hard drive — every Excel workbook, every CSV, every image — is ultimately a sequence of bytes. A byte is a unit of data made of 8 bits, and each byte can hold a value from 0 to 255. When you open a CSV, your text editor knows to interpret those bytes as characters. When you open a JPEG, your image viewer knows to interpret those bytes as pixel colors. The interpretation is what matters.

Think of it like a train with many carriages. The raw bytes are the train carriages. Whether carriage 1 is carrying passengers (a character), freight (an integer), or livestock (a timestamp) — that depends entirely on the timetable you're reading, which in binary format terms is called the format specification or schema.

In Power Query, a binary value is represented by the Binary.Type type. When you call File.Contents("myfile.bin"), you get back a value of this type — a blob of bytes that M hasn't tried to interpret yet. It's your job to tell M what those bytes mean.

Here's the key insight: binary parsing is just telling M where to look and how to interpret what it finds there. You point to a position (an offset), declare the data type (2-byte integer, 4-byte float, null-terminated string), and M hands you back a typed M value.

---

Reading a File as Binary Data

Let's start with the absolute basics. In Power Query's Advanced Editor, you can load any file as raw binary like this:

let
    RawBytes = File.Contents("C:\DataDump\sensor_log_2024.bin")
in
    RawBytes

What you get back isn't a table — it's a binary blob. Power Query will show you a single cell with a Binary value. That's your raw material.

The first thing you should almost always do after reading a file is buffer it. Buffering loads the entire file into memory at once rather than reading it lazily on demand. For binary parsing, where you'll be jumping around to different byte positions, this is critical for both performance and correctness.

let
    RawBytes    = File.Contents("C:\DataDump\sensor_log_2024.bin"),
    Buffered    = Binary.Buffer(RawBytes)
in
    Buffered

Binary.Buffer wraps the binary value in a memory buffer. Now every time you reference Buffered in subsequent steps, M reads from RAM instead of hitting the disk again. For a 500KB log file you might parse hundreds of times across different rows, this matters enormously.

Tip: Always buffer binary data before parsing it. Skipping this step is one of the most common causes of mysteriously slow binary parsing queries.

---

Slicing Bytes with Binary.Range

Now that you have your buffered binary blob, you need to navigate it. The function for this is Binary.Range.

Binary.Range(binary as binary, offset as number, optional count as number) as binary

• offset is the zero-based byte position where you want to start reading
• count is how many bytes you want to read

If your binary file starts with a 4-byte header at positions 0 through 3, and then has a 2-byte record count at positions 4 and 5, you'd extract them like this:

let
    Buffered    = Binary.Buffer(File.Contents("C:\DataDump\sensor_log_2024.bin")),
    Header      = Binary.Range(Buffered, 0, 4),
    RecordCount = Binary.Range(Buffered, 4, 2)
in
    RecordCount

But RecordCount is still a binary value — you just have 2 raw bytes. To turn those bytes into an actual M integer, you need to decode them.

---

Decoding Bytes into Typed Values with BinaryFormat

This is where things get powerful. The BinaryFormat library in M is a collection of functions that describe how to interpret a sequence of bytes. Think of each BinaryFormat.* function as a lens: you point it at some bytes, and it tells you what value is encoded there.

Reading Integers

The most common case is reading integers. Binary formats usually store integers in one of two byte orders (also called endianness):

• Little-endian (LE): The least significant byte comes first. This is what Intel/AMD processors use natively, and it's the most common format in modern files.
• Big-endian (BE): The most significant byte comes first. Common in network protocols and older mainframe formats.

If you have the bytes 0x01 0x00 (decimal: 1 and 0) and you read them as a little-endian 16-bit integer, you get 1. If you read the same bytes as big-endian, you get 256. Same bytes, wildly different values. This is why your format spec matters.

Here's how to read a 16-bit unsigned little-endian integer:

let
    Buffered    = Binary.Buffer(File.Contents("C:\DataDump\sensor_log_2024.bin")),
    RawCount    = Binary.Range(Buffered, 4, 2),
    RecordCount = BinaryFormat.UnsignedInteger16(RawCount)
in
    RecordCount

BinaryFormat.UnsignedInteger16 returns a function — and when you call it with your binary slice as an argument, it returns the decoded integer. Here's a reference for the most useful integer formats:

Function	Size	Signed?	Range
BinaryFormat.Byte	1 byte	No	0–255
BinaryFormat.SignedInteger16	2 bytes	Yes	−32,768 to 32,767
BinaryFormat.UnsignedInteger16	2 bytes	No	0–65,535
BinaryFormat.SignedInteger32	4 bytes	Yes	−2.1B to 2.1B
BinaryFormat.UnsignedInteger32	4 bytes	No	0–4.3B

Warning: All BinaryFormat integer readers assume little-endian by default. If your format uses big-endian integers, you'll need to either reverse the byte slice manually or handle the conversion arithmetically. We'll cover this shortly.

Reading Text

Strings in binary files come in two common flavors:

1. Fixed-length strings: Always exactly N bytes, padded with null characters (0x00) or spaces if shorter.
2. Null-terminated strings (C-style): Variable length, ending with a 0x00 byte.

For fixed-length text, read the bytes with Binary.Range, then decode with Text.FromBinary:

let
    Buffered    = Binary.Buffer(File.Contents("C:\DataDump\sensor_log_2024.bin")),
    // Sensor name is stored as 16 bytes starting at offset 6, ASCII encoded
    RawName     = Binary.Range(Buffered, 6, 16),
    SensorName  = Text.FromBinary(RawName, TextEncoding.Ascii)
in
    Text.TrimEnd(SensorName, Character.FromNumber(0))  // strip null padding

Text.TrimEnd with a null character removes trailing null padding. Always do this for fixed-length string fields — otherwise your column values will have invisible junk characters that break comparisons and lookups.

---

Handling Big-Endian Values

If your format spec says a 4-byte integer is big-endian and M only gives you little-endian readers, you reverse the bytes before parsing:

let
    Buffered       = Binary.Buffer(File.Contents("C:\DataDump\network_packet.bin")),
    RawInt         = Binary.Range(Buffered, 8, 4),
    ByteList       = Binary.ToList(RawInt),
    ReversedList   = List.Reverse(ByteList),
    ReversedBinary = Binary.FromList(ReversedList),
    ParsedInt      = BinaryFormat.UnsignedInteger32(ReversedBinary)
in
    ParsedInt

Binary.ToList converts a binary value into a list of byte values (integers 0–255). List.Reverse flips the order. Binary.FromList converts back. Then you parse normally. This pattern — ToList, transform, FromList — is your Swiss Army knife for any byte-level manipulation.

---

Building a Complete Binary Parser for a Realistic Format

Let's put everything together with a real scenario. Imagine a temperature monitoring system that writes sensor readings in the following binary format:

File Header (10 bytes total):

• Bytes 0–3: Magic number TEMP (ASCII, 4 bytes) — confirms this is a valid file
• Bytes 4–5: File version (UInt16 LE)
• Bytes 6–9: Number of records (UInt32 LE)

Each Record (14 bytes):

• Bytes 0–3: Unix timestamp (UInt32 LE) — seconds since 1970-01-01
• Bytes 4–7: Sensor ID (UInt32 LE)
• Bytes 8–11: Temperature reading (Int32 LE, scaled: divide by 100 to get °C)
• Bytes 12–13: Status flags (UInt16 LE)

Here's the complete parser:

let
    // ── Step 1: Load and buffer the file ─────────────────────────────────────
    RawBytes    = File.Contents("C:\DataDump\sensor_log_2024.bin"),
    Buffered    = Binary.Buffer(RawBytes),

    // ── Step 2: Validate the file header ─────────────────────────────────────
    MagicBytes  = Binary.Range(Buffered, 0, 4),
    MagicText   = Text.FromBinary(MagicBytes, TextEncoding.Ascii),
    IsValid     = if MagicText = "TEMP" then true else error "Not a valid TEMP sensor file",

    // ── Step 3: Read header metadata ─────────────────────────────────────────
    FileVersion = BinaryFormat.UnsignedInteger16(Binary.Range(Buffered, 4, 2)),
    RecordCount = BinaryFormat.UnsignedInteger32(Binary.Range(Buffered, 6, 4)),

    // ── Step 4: Parse each record using List.Generate ─────────────────────────
    HeaderSize  = 10,
    RecordSize  = 14,

    RecordList  = List.Generate(
        () => 0,                                        // start at record index 0
        (i) => i < RecordCount,                         // keep going until done
        (i) => i + 1,                                   // increment record index
        (i) =>                                          // transform: build a record
            let
                Offset      = HeaderSize + (i * RecordSize),
                RecordBytes = Binary.Range(Buffered, Offset, RecordSize),

                // Timestamp: UInt32 LE → convert Unix epoch to M datetime
                RawTimestamp = BinaryFormat.UnsignedInteger32(
                                   Binary.Range(RecordBytes, 0, 4)),
                Timestamp    = #datetime(1970, 1, 1, 0, 0, 0)
                                   + #duration(0, 0, 0, RawTimestamp),

                // Sensor ID
                SensorID     = BinaryFormat.UnsignedInteger32(
                                   Binary.Range(RecordBytes, 4, 4)),

                // Temperature: Int32 LE, divide by 100
                RawTemp      = BinaryFormat.SignedInteger32(
                                   Binary.Range(RecordBytes, 8, 4)),
                Temperature  = RawTemp / 100,

                // Status flags
                StatusFlags  = BinaryFormat.UnsignedInteger16(
                                   Binary.Range(RecordBytes, 12, 2))
            in
                [
                    Timestamp   = Timestamp,
                    SensorID    = SensorID,
                    Temperature = Temperature,
                    StatusFlags = StatusFlags
                ]
    ),

    // ── Step 5: Convert list of records to a table ────────────────────────────
    ResultTable = Table.FromRecords(RecordList),

    // ── Step 6: Set column types ──────────────────────────────────────────────
    TypedTable  = Table.TransformColumnTypes(ResultTable, {
        {"Timestamp",   type datetime},
        {"SensorID",    Int64.Type},
        {"Temperature", type number},
        {"StatusFlags", Int64.Type}
    })
in
    TypedTable

Let's walk through the key decisions here.

Why List.Generate instead of a fixed range? Because we read RecordCount from the file itself. The file tells us how many records it contains, so we don't hardcode anything. This handles files with 5 records and files with 50,000 records identically.

Why convert timestamps inside the record parsing loop? Because keeping the logic together makes the code self-documenting. Each field's derivation lives right next to its raw read. When you come back to this query in six months, you'll understand it immediately.

The Unix timestamp conversion: #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, RawTimestamp) — this adds RawTimestamp seconds to the Unix epoch. It's a clean idiom that M handles natively without any external functions.

Tip: The IsValid step uses M's error keyword. If the magic number check fails, the entire query fails with a meaningful message rather than silently producing garbage data. Always validate your binary headers.

---

Using BinaryFormat.Record for Cleaner Parsing

M also provides a higher-level approach using BinaryFormat.Record, which lets you declare a record schema and parse it in one shot:

let
    Buffered    = Binary.Buffer(File.Contents("C:\DataDump\sensor_log_2024.bin")),
    
    // Define the record format
    RecordFormat = BinaryFormat.Record([
        RawTimestamp = BinaryFormat.UnsignedInteger32,
        SensorID     = BinaryFormat.UnsignedInteger32,
        RawTemp      = BinaryFormat.SignedInteger32,
        StatusFlags  = BinaryFormat.UnsignedInteger16
    ]),

    // Read starting at byte 10 (past the header)
    FirstRecord  = RecordFormat(Binary.Range(Buffered, 10, 14))
in
    FirstRecord

BinaryFormat.Record reads fields sequentially — it doesn't use explicit offsets. Instead, it consumes bytes in order based on each field's declared size. This is elegant when your format is truly sequential with no gaps.

You can also use BinaryFormat.List to repeat a format N times:

let
    Buffered      = Binary.Buffer(File.Contents("C:\DataDump\sensor_log_2024.bin")),
    RecordCount   = BinaryFormat.UnsignedInteger32(Binary.Range(Buffered, 6, 4)),
    
    SingleRecord  = BinaryFormat.Record([
        RawTimestamp = BinaryFormat.UnsignedInteger32,
        SensorID     = BinaryFormat.UnsignedInteger32,
        RawTemp      = BinaryFormat.SignedInteger32,
        StatusFlags  = BinaryFormat.UnsignedInteger16
    ]),

    AllRecords    = BinaryFormat.List(SingleRecord, RecordCount),
    
    // Parse the data section (skip 10-byte header)
    ParsedList    = AllRecords(Binary.Range(Buffered, 10, Binary.Length(Buffered) - 10))
in
    Table.FromRecords(ParsedList)

BinaryFormat.List takes a format and a count and returns a list of decoded records. This is more concise than List.Generate when the format is uniform. The tradeoff: BinaryFormat.Record and BinaryFormat.List can be harder to debug when something goes wrong, because the error messages are less specific than explicit offset-based parsing.

Warning: Binary.Length has to read the entire buffer to return the byte count. Call it once, store it in a step, and reuse the value rather than calling it multiple times.

---

Hands-On Exercise

Create a new blank query in Power Query (in Excel: Data tab → Get Data → From Other Sources → Blank Query, then open the Advanced Editor; in Power BI Desktop: Home → Transform Data → New Source → Blank Query, then Advanced Editor).

Your task is to write a query that parses the following simulated binary structure. We'll use Binary.FromList to create test data in-memory rather than reading a file, so you can do this without any external files.

The format: A simple employee badge log where each entry is 8 bytes:

• Bytes 0–3: Employee ID (UInt32 LE)
• Bytes 4–5: Door ID (UInt16 LE)
• Bytes 6–7: Hour and minute (one byte each, UInt8)

Step 1: Create test binary data using this expression:

let
    // Simulate 3 badge-swipe records as raw bytes
    // Employee 1001, Door 3, Time 08:45
    // Employee 2034, Door 7, Time 14:22
    // Employee 578,  Door 1, Time 09:00
    SimulatedBytes = Binary.FromList({
        233, 3, 0, 0,   // 1001 in little-endian UInt32
        3, 0,           // Door 3 as UInt16
        8, 45,          // Hour 8, Minute 45

        210, 7, 0, 0,   // 2034 in little-endian UInt32
        7, 0,           // Door 7 as UInt16
        14, 22,         // Hour 14, Minute 22

        66, 2, 0, 0,    // 578 in little-endian UInt32
        1, 0,           // Door 1 as UInt16
        9, 0            // Hour 9, Minute 0
    }),
    Buffered = Binary.Buffer(SimulatedBytes)
in
    Buffered

Step 2: Extend this query to parse all three records into a table with columns EmployeeID, DoorID, Hour, Minute, and a calculated SwipeTime column that formats as text like "08:45".

Challenge: Make the parser work for any number of records by computing RecordCount = Binary.Length(Buffered) / 8 and using List.Generate rather than hardcoding 3.

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting that offsets are zero-based If your spec says a field is at "byte 5," that means offset 4 in M. Format specs often use 1-based byte numbering, but Binary.Range uses 0-based offsets. Always subtract 1.

Mistake 2: Not buffering before parsing Parsing without Binary.Buffer causes M to re-read from disk on every access. With large files or many records, this turns a 2-second query into a 20-minute one.

Mistake 3: Using the wrong endianness If your parsed values look like nonsense large numbers when they should be small ones (or vice versa), check the byte order. Swap between big-endian and little-endian interpretation using the Binary.ToList → List.Reverse → Binary.FromList pattern.

Mistake 4: Forgetting to strip null padding from strings Fixed-length string fields padded with 0x00 bytes will appear equal to each other in a preview but fail equality comparisons in DAX or when writing to a database. Always Text.TrimEnd with Character.FromNumber(0).

Mistake 5: Treating BinaryFormat functions as values instead of calling them BinaryFormat.UnsignedInteger16 is a function. BinaryFormat.UnsignedInteger16(someBytes) is the decoded integer. A very common mistake is writing = BinaryFormat.UnsignedInteger16 (missing the argument) and getting a function value instead of a number.

Debugging tip: When a parse goes wrong, isolate a single record. Hardcode Offset = 10 and parse just that one record completely before worrying about the loop. Verify each field individually before combining them into the full record structure.

---

Summary & Next Steps

You've covered a lot of ground. You now understand that binary data is just bytes waiting to be interpreted, and that Power Query's M language gives you the full toolkit to do that interpretation yourself: File.Contents and Binary.Buffer for loading, Binary.Range for slicing, BinaryFormat.* for decoding typed values, and List.Generate or BinaryFormat.List for repeating that logic across all records.

The pattern you've learned generalizes to virtually any binary format:

1. Load and buffer the file
2. Validate any header magic bytes
3. Read metadata from the header (record count, version, offsets)
4. Loop over records using List.Generate or BinaryFormat.List
5. Within each iteration, calculate the byte offset, slice the bytes, decode each field
6. Convert the list of records to a table and set types

Where to go next:

• Explore BinaryFormat.Choice for parsing variable-structure binary data where the format of one section depends on a value read earlier
• Learn how Power Query's built-in connectors use these same primitives under the hood by reading Microsoft's open-source connector SDK
• Practice with well-documented binary formats like BMP image files or WAV audio headers, which have publicly available specs and are simple enough to parse in under 50 lines of M
• Look into combining binary parsing with folder queries using Folder.Files to process an entire directory of binary logs in a single query

Binary parsing is one of those skills that feels arcane until the first time you actually need it — and then it's indispensable. Now you have it.