Excel Power Query Exporter

Export Power Query M scripts from Excel workbooks into one .pq file per query.

This project is designed for version control and bulk inspection of Power Query logic without opening Excel. It reads the workbook package directly and extracts the M definitions stored in the DataMashup part.

Features

• Supports common OOXML Excel formats such as .xlsx, .xlsm, .xltx, .xltm, .xlam, and .xlsb
• Accepts a single file or a folder
• Optional recursive folder traversal
• Writes one .pq file per query
• Preserves folder structure during batch exports
• Attempts a temporary snapshot copy when a workbook is open in Excel
• Uses only the Python standard library

Install

Run without installing

python excel_pq_export.py "path/to/workbook.xlsx"

Install as a CLI

pip install .
excel-pq-export "path/to/workbook.xlsx"

Build EXE

If you want a standalone Windows executable, use PyInstaller.

Install PyInstaller:

pip install pyinstaller

Build the exe from the repo root:

.\build_exe.ps1

Or run PyInstaller directly:

pyinstaller --clean --noconfirm .\excel-pq-export.spec

Output:

dist\excel-pq-export.exe

If you launch the exe without command-line arguments, it will prompt for:

• input workbook or folder path
• optional output directory
• whether folder scanning should be recursive

Usage

excel-pq-export INPUT [-o OUTPUT] [-r]

Arguments

• INPUT: path to an Excel workbook or a folder containing workbooks
• -o, --output: output directory
• -r, --recursive: when INPUT is a folder, scan subfolders recursively

Output behavior

Single workbook input

If INPUT is a workbook and --output is omitted, output goes to:

<workbook_parent>/<workbook_stem>_pq/

Example:

report.xlsx -> report_pq/

Folder input

If INPUT is a folder and --output is omitted, each workbook is exported next to itself:

<workbook_parent>/<workbook_stem>_pq/

When --output is provided, each workbook gets its own export directory under that output root. With --recursive, the relative subfolder structure beneath the input folder is preserved under the output root.

Example:

input/
  north/report.xlsx
  south/model.xlsm

excel-pq-export input --recursive --output exported

exported/
  north/report_pq/
  south/model_pq/

Examples

Export one workbook:

excel-pq-export "C:\data\model.xlsx"

Export every workbook in a folder:

excel-pq-export "C:\data\workbooks"

Export every workbook in a folder tree:

excel-pq-export "C:\data\workbooks" --recursive

Export to a specific directory:

excel-pq-export "C:\data\workbooks" --recursive --output "C:\exports"

Notes

• This tool targets workbook formats based on the Office Open XML package structure.
• Legacy .xls files are not supported.
• Password-protected or corrupted files will fail to extract.
• If a workbook is open and direct access is denied, the tool attempts to copy it to a temporary file first.
• Query file names are sanitized for cross-platform filesystem safety.

Why this works

Microsoft documents Power Query workbook formulas as part of the DataMashup stream stored in a Custom XML part within the workbook package. The actual M formulas are stored in /Formulas/Section1.m inside that stream.

Development

Run tests with:

python -m unittest discover -s tests

Rebuild the Windows executable with:

.\build_exe.ps1

License

MIT