Excel使用Power Query转换表1为表2时大数据量运行慢如何解决

现有表1格式的原始数据，需转换为表2的目标结构，当前使用Power Query编写转换逻辑，但处理大规模数据集时运行速度极慢，需要可行的优化方案。
转换前后结构参考：

• 精简Power Query计算链路
  第一步就删除所有和转换逻辑无关的冗余列，全程不要保留无用字段；关闭Power Query自动检测数据类型功能，手动为每列指定固定数据类型，避免程序逐行扫描识别类型产生的额外开销；尽量避免使用逐行执行的自定义M函数，所有逻辑优先调用Power Query原生内置表操作函数，原生函数底层为C++实现，性能远高于M语言逐行解释执行。
• 替换低效转换写法
  从表结构差异判断，本次转换核心是宽表转长表的维度匹配逻辑，不要使用多次Table.Transpose（表转置）操作，转置对大表的内存开销极高，直接使用Table.UnpivotOtherColumns函数完成单次逆透视即可，比多步转置+筛选的写法性能提升10倍以上。
• 合理使用内存缓冲
  对转换流程中会被多次引用的静态中间结果表，使用Table.Buffer函数提前将其加载到内存中，避免Power Query重复拉取数据源、重复计算相同中间结果；注意不要滥用缓冲函数，支持查询折叠的步骤前不要加缓冲，否则会打断查询折叠逻辑，反而降低性能。
• 启用查询折叠优化
  如果原始数据存储在SQL Server、结构化数据表等支持查询折叠的数据源中，将删列、筛选、数据类型转换这类简单操作放在查询步骤的最前端，让这类操作直接在数据源端执行完成，不需要把全量原始数据拉到本地内存再处理，数据源端的计算性能远高于本地M语言执行效率。
• 超大数据集替换实现方案
  如果数据量级达到百万行以上，直接放弃Power Query作为转换工具，选择性能更高的实现方式：
  • 用pandas做转换的核心参考代码：

    import pandas as pd
    # 加载原始表1数据
    raw_df = pd.read_excel("原始数据文件.xlsx", sheet_name="表1")
    # 逆透视非维度列，快速生成目标长表结构
    target_df = raw_df.melt(
        id_vars=["需要保留的固定维度列1", "需要保留的固定维度列2"],
        var_name="属性字段名",
        value_name="数值字段名"
    )
    # 补充后续清洗匹配逻辑后导出
    target_df.to_excel("转换后表2.xlsx", index=False)
    

  • 如果数据存储在数据库中，直接写SQL用UNPIVOT/PIVOT语法完成结构转换，性能可达Power Query的百倍以上。

注意：Power Query中不要加入任何逐行调整单元格格式、设置条件格式的逻辑，这类逐行渲染类操作在数据量大时会直接导致程序卡顿。

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