引言：大数据量下的Excel性能困境
在现代企业办公环境中，Power Query已成为数据预处理的核心工具。然而，当处理数万行甚至百万级数据时，许多用户会发现数据刷新变得异常缓慢，甚至导致Excel假死。这通常并非硬件瓶颈，而是M语言编写方式不当导致的逻辑冗余。本文旨在从技术底层出发，探讨如何通过优化M语言脚本结构，实现性能的显著提升。
核心痛点：为何查询会变慢？
Power Query基于列式存储和惰性求值机制，但其引擎对复杂的转换操作处理能力有限。常见的性能杀手包括：
- 过度使用自定义函数：在每一行调用外部自定义函数，会导致上下文切换开销激增。
- 频繁的“追加查询”：将多个结构相似但来源不同的查询合并，若未正确优化，会产生大量中间步骤。
- 复杂的列添加与合并：在宽表中反复添加计算列并进行字符串拼接，会消耗大量内存资源。
优化策略一：避免逐行调用自定义函数
初级用户常习惯为每个单元格编写自定义M函数来处理复杂逻辑。例如，在一个包含10万条订单记录的表中，如果为每一行调用一个计算折扣价的函数，引擎需要执行10万次函数调用，效率极低。
优化方案：应将逻辑向量化或改用内置函数。如果必须使用自定义逻辑，建议将其封装为Table类型的聚合函数，而非Record类型的行级函数。通过创建参数化查询，一次性传入整个列进行批量处理，可以大幅减少引擎的调度负担。
代码示例对比
低效写法：
- 定义函数
fn_CalcPrice，接受单行记录。 - 在查询中使用
Table.AddColumn(Source, "Custom", each fn_CalcPrice(_))。
高效写法：
- 直接使用内置表达式：
Table.TransformColumns(Source, {"Price", each _ * 0.9})。 - 或者使用
List.Accumulate进行批处理逻辑，避免逐行迭代。
优化策略二：减少查询合并次数与优化连接逻辑
在处理多维数据源时，用户倾向于使用“合并查询”功能模拟SQL Join操作。然而，多次嵌套合并或在不必要的列上建立连接，会显著增加计算复杂度。
1. 选择合适的连接类型
根据业务需求选择Inner Join、Left Outer Join或Full Outer Join。默认情况下，Power Query使用Left Outer Join，这会保留左表所有数据并尝试匹配右表。如果只需要交集数据，改为Inner Join可以减少后续的数据过滤步骤。
2. 最小化连接键数量
连接操作是基于哈希表或排序算法实现的。连接的列越多，构建哈希表的内存开销越大。仅使用能够唯一标识关系的必要列作为连接键。例如，如果“订单ID”足以唯一确定关系，切勿额外添加“日期”或“客户名称”作为联合主键。
3. 使用索引列进行非键连接
有时我们需要根据行号或非主键字段进行匹配。此时，可以先为两个表添加索引列，然后基于索引列进行合并。这种方法比直接对文本列进行模糊匹配或复杂条件判断要高效得多。
优化策略三：利用“按列填充”与“逆透视”简化数据结构
数据结构的复杂性直接影响查询性能。宽表（很多列）和多层嵌套结构是性能的大敌。
1. 逆透视（Unpivot）优于条件列
当需要将多个同类型列（如1月销售额、2月销售额...）转换为两列（月份、销售额）时，使用内置的“逆透视列”功能比手动添加数十个条件列（If/Else）要快且易于维护。逆透视操作在底层是基于列元数据的重组，计算成本极低。
2. 按需加载（Lazy Loading）
确保仅在最后一步启用“仅加载到此”，而在中间步骤禁用“启用加载”。这样，Power Query引擎不会将中间转换结果写入Excel工作表，仅在最终刷新时生成所需数据，极大节省I/O开销。
高级技巧：监控与调试性能瓶颈
为了精准定位慢查询，建议使用以下方法进行分析：
- 查看查询依赖图：在Power Query编辑器中，点击“视图”->“查询依赖项”。检查是否有循环引用或过于复杂的网状结构。
- 分析数据预览：在每一步转换后，观察右下角显示的“估计行数”和“数据类型”。如果某一步骤行数激增，说明产生了笛卡尔积，需立即调整连接逻辑。
- 使用Power BI Desktop Profiler：虽然主要用于BI工具，但其日志记录功能可帮助识别哪一行M代码执行耗时最长。
结语
Power Query的性能优化不仅仅是对代码的微调，更是对数据处理思维的重塑。通过理解M语言的执行原理，避免逐行操作，精简连接逻辑，并合理规划数据模型，用户可以显著提升Excel报表的响应速度。对于中小企业IT管理员而言，将这些最佳实践标准化并推广至用户群体，是提升整体办公效率的关键举措。