The Complete Guide to the Invoke.XlsxReader Function The Invoke.XlsxReader function is a powerful, low-level tool in Microsoft Power Query and M formula language. It allows developers to read and extract data directly from Excel Open XML (.xlsx) files.
While most users rely on the standard Excel.Workbook function, understanding Invoke.XlsxReader provides deeper control over data ingestion, especially when optimizing performance or handling complex file structures.
This guide covers the syntax, inner workings, use cases, and practical examples of the Invoke.XlsxReader function. What is Invoke.XlsxReader?
In Power Query, Excel.Workbook is a high-level wrapper. Under the hood, when dealing with modern Excel files, Power Query utilizes internal XML reading capabilities to parse the zipped XML structure of a .xlsx file.
Invoke.XlsxReader exposes this underlying functionality. It treats the Excel file as a compressed archive of XML documents and provides direct access to the raw worksheets, tables, and defined names stored inside. Syntax and Parameters
The function typically follows this structural format in M code: powerquery
Invoke.XlsxReader(content as binary, optional options as nullable record) as table Use code with caution. Parameters
content: The file content passed as binary data. This is usually obtained via File.Contents or Web.Contents.
options: An optional record to configure specific reading behaviors (e.g., handling specific formatting, culture, or performance flags). Return Value
The function returns a navigation table containing the components of the Excel workbook. The returned table generally includes columns such as: Name: The name of the sheet, table, or defined range.
Data: The actual content (nested tables) of that specific object. Item: The internal identifier.
Kind: The type of object (e.g., “Sheet”, “Table”, “DefinedName”). Invoke.XlsxReader vs. Excel.Workbook Excel.Workbook Invoke.XlsxReader Abstraction Level High-level, user-friendly wrapper Low-level, direct engine access Performance Optimized for general use Faster for massive, raw XML data streams Type Inference Automatically attempts to detect headers/types Returns raw, un-pivoted, or untyped data structures Use Case Standard data transformations Advanced performance tuning and custom connectors Step-by-Step Implementation Example
Here is how you can use Invoke.XlsxReader within the Power Query Advanced Editor to extract data from a local Excel file. Step 1: Source the Binary Data First, pull the file into Power Query as binary content. powerquery
let SourceFile = File.Contents(“C:\Data\FinancialReport.xlsx”), Use code with caution. Step 2: Invoke the Reader Pass the binary source into the Invoke.XlsxReader function. powerquery WorkbookContents = Invoke.XlsxReader(SourceFile), Use code with caution. Step 3: Filter for the Target Sheet
Filter the navigation table to find the specific sheet or table you need. powerquery
TargetSheet = WorkbookContents{[Name=“SalesData”, Kind=“Sheet”]}[Data], Use code with caution. Step 4: Promote Headers and Clean
Because the reader extracts raw data, you must manually promote headers and assign data types. powerquery
PromotedHeaders = Table.PromoteHeaders(TargetSheet, [PromoteAllScalars=true]) in PromotedHeaders Use code with caution. Best Practices and Performance Tips
Use for Large Files: If Excel.Workbook throws out-of-memory errors on massive datasets, switching to Invoke.XlsxReader can bypass some memory overhead by avoiding high-level metadata evaluation.
Explicitly Define Types: Because low-level readers do not guess column types reliably, always append a Table.TransformColumnTypes step immediately after promoting headers to ensure data integrity.
Buffer When Necessary: If you are reading the same file multiple times across different queries, use Binary.Buffer(File.Contents(…)) before invoking the reader to prevent repeated disk I/O operations.
To help tailor this guide to your specific project, tell me:
What specific error or performance issue brought you to this function?
Leave a Reply