含300+Power Query查询的Excel文件加载缓慢求优化方案

当Excel包含300+Power Query查询时，每个查询的定义以XML格式存储，.NET Framework需要逐个解析这些XML结构，查询数量过多会导致解析耗时剧增，进而出现长时间显示“Loading .NET Framework”且无法操作的情况。以下是可保留所有查询的优化方案：

• 合并重复逻辑的查询：将逻辑相似或重复的查询整合为参数化函数，减少总查询数量。比如多个从不同工作表取数但处理流程一致的查询，可替换为一个接受工作表名称参数的自定义函数，其他查询直接调用该函数即可。
  示例代码：

  (SheetName as text) as table => 
  let
      Source = Excel.CurrentWorkbook(){[Name=SheetName]}[Content],
      // 通用处理步骤
      CleanData = Table.RemoveColumns(Source, {"冗余列"}),
      TransformedData = Table.TransformColumnTypes(CleanData, {{"日期", type date}})
  in
      TransformedData
  

• 设置查询为仅创建连接：对不需要加载到工作表的查询，右键选择「加载到」，设置为「仅创建连接」。这样Excel启动时不会自动加载这些查询的数据集，大幅降低.NET的处理负载。

• 精简查询步骤：逐个检查查询的步骤列表，移除不必要的中间步骤、注释、未使用的自定义函数或变量，减少XML结构的复杂度，加快.NET解析速度。

• 升级.NET Framework至最新兼容版本：安装对应Excel版本支持的最新.NET Framework（如Excel 2016及以上推荐.NET 4.8），新版本在XML解析性能上有针对性优化，能有效缩短加载耗时。

• 拆分工作簿分散查询压力：若合并查询不可行，可将部分查询拆分到独立的Excel工作簿，通过Power Query的「从文件>从工作簿」功能引用其他文件的查询，分散单个文件的查询数量压力。

• 禁用后台数据刷新：打开Excel选项>数据>查询加载，取消勾选「允许后台数据刷新」，避免启动时同时触发多个查询的数据刷新，集中资源处理.NET加载流程。

• 清理Power Query缓存：在Power Query编辑器中，依次点击「文件>选项和设置>查询选项>数据加载>清除缓存」，减少缓存文件对加载过程的干扰。

内容的提问来源于stack exchange，提问作者Feifan Wang