从 Excel 的 Power Query：迁移到 BI 和 R7

从 Excel 提取 Power Query：自动化迁移至 BI 和 R7 Office

Excel 结合 Power Query 和 VBA 常用于数据应用的原型开发。随着数据量激增，文件变成“黑盒子”：转换逻辑深埋在无数对象中，数据源硬编码到环境中，修改风险极高。迁移到 BI 系统或 R7 Office 需要彻底剖析 M 代码、依赖关系和数据源。

任何带 Power Query 的 Excel 文件都有三个关键问题：

• 数据从哪里来？
• 转换步骤的执行顺序是什么？
• 删除某个步骤会破坏什么？

回答这些问题需要手动分析数十个查询，极大增加认知负担和审计时间。

Power Query 界面为何不适合分析

Power Query 是基于 M 语言的管道，存储在 .xlsx 文件的 XML 中。界面适合构建步骤，但审计无用：缺少依赖图，血缘追踪困难。编辑需在查询间跳跃，易断链路。

解决方案：从手动拆解转向自动化提取。.xlsx 本质是 ZIP 存档，包含 M 代码的 XML 结构。解析可构建抽象语法树 (AST)，重构依赖图，并提取源参数（文件、数据库、连接器）。

提取的技术实现

整个过程无需启动 Excel：

• 解压存档：提取 .xlsx 中的 Power Query XML 查询。
• 解析 M 代码：将其拆解为结构化元素，而非纯文本——捕获步骤、函数和引用。
• 构建图谱：映射查询间及与源的依赖关系。
• 导出产物：数据至 CSV/DuckDB，元数据至 JSON（逻辑、血缘、连接）。

结果：独立数据模型，源透明、依赖明确、变更可预测。无需重写即可导入 BI。

| 指标 | 手动审查 | 自动化分析 |

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

| 复杂文件耗时 | 6+ 小时 | 1 小时 |

| 遗漏依赖风险 | 高 | 极低 |

| 迁移准备度 | 从零重写 | 血缘图 + 导出 |

迁移至 BI 系统

无需手动重现逻辑，直接导入提取模型：

• 从导出直载数据。
• 转移依赖作为即用管道。
• 适配源而不丢细节。

这将迁移从研究苦活转为工程任务：BI 获结构化数据及完整血缘文档。

适配 R7 Office 及低代码/无代码平台

R7 Office 不原生支持 Power Query。提取的 M 代码结构作为基础，翻译为 JavaScript 或目标系统原生脚本。中介层解耦逻辑与 Excel，提升可移植性。

优势：

• 自动文档化：含步骤和参数的 JSON。
• 源审计：无需运行文件即可检查连接。
• 重构：调整图谱不破坏整体。

静态分析的局限

自动化覆盖 90% 场景，但标记棘手案例：

• 动态代码：Expression.Evaluate、路径拼接、条件编译。
• 外部调用：VBA 集成、自定义连接器。
• 版本差异：Excel 2016/365 vs Power BI Desktop。

这些需手动审查。

核心要点

• 提取 Power Query 将隐式逻辑转为带血缘的结构化模型。
• 分析时间减 6 倍，风险最小化。
• 简化 BI 迁移（直导）和 R7 Office（JS 转换）。
• 无需 Excel 即可审计、重构和文档化。
• 局限：动态代码及外部调用需手动检查。

— Editorial Team