Power Query from Excel: migration to BI and R7

Extracting Power Query from Excel: Automating Migration to BI and R7 Office

Excel with Power Query and VBA is often used for prototyping data apps. As data volumes grow, the file turns into a 'black box': transformation logic is buried in countless objects, data sources are hardcoded to the environment, and changes are risky. Migrating to BI systems or R7 Office requires fully dissecting M code, dependencies, and sources.

Three key questions for any Excel file with Power Query:

• Where does the data come from?
• What order are transformations applied in?
• What will break if you delete a step?

Answering these demands manual analysis of dozens of queries, ramping up cognitive load and audit time.

Why the Power Query Interface Falls Short for Analysis

Power Query is a pipeline in the M language, stored in .xlsx as XML. The UI is great for building steps but useless for auditing: no dependency graphs, hard to track lineage. Editing means jumping between queries, risking broken links.

Solution: Shift from manual dissection to automated extraction. .xlsx is a ZIP archive with XML structures of M code. Parsing builds an abstract syntax tree (AST), reconstructs the dependency graph, and pulls source parameters (files, databases, connectors).

Technical Implementation of Extraction

The process runs without launching Excel:

• Unzip the archive: Extract .xlsx and pull Power Query XML queries.
• Parse M code: Break it down as structures, not text—capturing steps, functions, and references.
• Build the graph: Map dependencies between queries and sources.
• Export artifacts: Data to CSV/DuckDB, metadata to JSON (logic, lineage, connections).

Result: An independent data model with transparent sources, explicit dependencies, and predictable changes. It's ready for BI import without rewriting.

| Metric | Manual Review | Automated Analysis |

|---------|---------------|---------------------|

| Time for complex file | 6+ hours | 1 hour |

| Risk of missing dependencies | High | Minimal |

| Migration readiness | Rewrite from scratch | Lineage map + export |

Migrating to BI Systems

Instead of manually recreating logic, plug in the extracted model:

• Load data straight from exports.
• Transfer dependencies as a ready pipeline.
• Adapt sources without losing details.

This turns migration from a research slog into engineering work: BI gets structured data with full lineage docs.

Adapting for R7 Office and Low-Code/No-Code

Power Query isn't natively supported in R7 Office. The extracted M code structure becomes the base for translating to JavaScript or native scripts in the target system. An intermediate layer decouples logic from Excel, making it portable.

Benefits:

• Automatic documentation: JSON with steps and params.
• Source auditing: Check connections without running the file.
• Refactoring: Tweak the graph without breaking things.

Limits of Static Analysis

Automation handles 90% of cases but flags tricky ones:

• Dynamic code: Expression.Evaluate, path concatenation, conditional compilation.
• External calls: VBA integration, custom connectors.
• Version differences: Excel 2016/365 vs Power BI Desktop.

These get marked for manual review.

Key Takeaways

• Extracting Power Query turns hidden logic into a structured model with lineage.
• Cuts file analysis time by 6x, minimizes risks.
• Streamlines BI migration (direct import) and R7 Office (JS translation).
• Enables auditing, refactoring, and docs without Excel.
• Limits: Dynamic code and external calls need manual checks.

— Editorial Team