如何缩短Power Query中应用步骤的切换延迟？

问题症状 #

• 仅点击应用步骤查看结果，UI渲染耗时可达10分钟以上
• 新增步骤时，Power Query会重新加载所有前置步骤，状态栏显示从CSV源加载的数据量远大于原文件
• 即使使用仅200行的本地样本文件，查看步骤、重命名步骤或删除列等简单操作仍卡顿10-30秒
• 注：问题仅出现在Power Query编辑器开发阶段，加载到工作表或刷新查询时无异常

已尝试的优化操作 #

• 将SharePoint上的3个.xlsx源文件改为本地C盘文件
• 使用仅含200行数据的本地样本文件
• 将源数据移入当前工作簿
• 转换为本地C盘的.csv源文件（仅获得小幅改善）
• 调整Power Query设置：
  • 启用「Never detect column types and headers for unstructured sources」
  • 清除缓存
  • 启用「Never allow data previews to download in the background」
  • 启用「Always ignore Privacy Level settings」

相关M代码 #

let
    Source = Folder.Files("C:\PATH TO CSV DATA FILE"),
    
    /* auto generated when I loaded the data source using the UI buttons */
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    /* end auto gen M code */
    
    #"Removed Top Rows" = Table.Skip(#"Expanded Table Column1",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Fixed Column Header Names" = Table.RenameColumns(#"Promoted Headers",{{"Project Task ", "Project Task"}, {"Expenditure Type ", "Expenditure Type"}, {"Org ", "Org"}, {"Projects ", "Projects"}, {"Activity ", "Activity"}, {"Program ", "Program"}, {"Account ", "Account"}, {"FinancialUnit ", "FinancialUnit"}, {"NetAssetClass ", "NetAssetClass"}, {"Entity ", "Entity"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Fixed Column Header Names",{"Business Unit", "Department", "Payroll", "E_Number", "E_Name", "Assignment Number", "Element Classification", "Element", "Entity", "NetAssetClass", "FinancialUnit", "Account", "Program", "Activity", "Projects", "Org", "Expenditure Type", "Project Task", "Process", "Project Name", "Project Type", "Period Start Date", "Period End Date", "Process Date", "Date Earned", "Accounting Date", "Debit Amount", "Credit Amount"}),
    #"Filter Elements" = Table.SelectRows(#"Removed Other Columns", each List.Contains(lCostingElementClassifications,[Element])),
    #"Changed Type" = Table.TransformColumnTypes(#"Filter Elements",{{"Debit Amount", type number}, {"Credit Amount", type number}}),
    #"Added TOTAL AMOUNT"= Table.AddColumn(#"Changed Type", "TOTAL AMOUNT", each [Debit Amount] - [Credit Amount], Currency.Type),
    #"Removed Debit Credit Columns" = Table.RemoveColumns(#"Added TOTAL AMOUNT",{"Debit Amount", "Credit Amount"}),
    #"Added EFFORT COSTING PROJECT" = Table.AddColumn(#"Removed Debit Credit Columns", "PC NUMBER", each if [Project Type] = "" then [Entity] & "." & [NetAssetClass] & "." & [FinancialUnit] & "." & [Account] & "." & [Program] & "." & [Activity] & "." else if List.Contains( {"Cores", "Gift and Endowment Temporary", "Gift and Endowment Unrestricted"}, [Project Type]) then [Projects] & "." & [Project Task] & "." else [Projects] & "." & [Project Task] & "."),
    #"Merged Queries" = Table.NestedJoin(#"Added EFFORT COSTING PROJECT", {"PC NUMBER"}, qProjectsML, {"PN.TN."}, "qProjectsML", JoinKind.LeftOuter),
    #"Expanded qMasterList" = Table.ExpandTableColumn(#"Merged Queries", "qProjectsML", {"NET ASSET CLASS (NAC)", "Program", "Activity"}, {"qProjectsML.NET ASSET CLASS (NAC)", "qProjectsML.Program", "qProjectsML.Activity"}),
    #"Added TRUE NAC" = Table.AddColumn(#"Expanded qMasterList", "TRUE NAC", each if [Projects] = "0000" then [NetAssetClass] else [qProjectsML.NET ASSET CLASS (NAC)]),
    #"Added TRUE Program" = Table.AddColumn(#"Added TRUE NAC", "TRUE PROGRAM", each if [Projects] = "0000" then [Program] else [qProjectsML.Program]),
    #"Added TRUE Activity" = Table.AddColumn(#"Added TRUE Program", "TRUE ACTIVITY", each if [Projects] = "0000" then [Activity] else [qProjectsML.Activity]),
    #"Merged Queries1" = Table.NestedJoin(#"Added TRUE Activity", {"E_Number"}, qRosterML, {"Person Number"}, "qRosterML", JoinKind.LeftOuter),
    #"Expanded qRosterML" = Table.ExpandTableColumn(#"Merged Queries1", "qRosterML", {"Work Unit", "Job Code", "Job Title", "Position Name", "JCAT", "Full/Part Time", "Regular/Temporary"}, {"qRosterML.Work Unit", "qRosterML.Job Code", "qRosterML.Job Title", "qRosterML.Position Name", "qRosterML.JCAT", "qRosterML.Full/Part Time", "qRosterML.Regular/Temporary"})
in
    #"Expanded qRosterML"

针对性优化建议 #

1. 优化文件夹加载逻辑 #

原代码通过Folder.Files加载整个文件夹，结合自动生成的Transform File函数，可能导致重复解析文件元数据和内容：

• 如果只需要处理单个CSV文件，直接指定文件路径替代文件夹加载，测试卡顿是否缓解：

  Source = Csv.Document(File.Contents("C:\PATH TO CSV DATA FILE\your-target-file.csv"), [Delimiter=",", Encoding=1252])
  

• 若必须加载文件夹，提前过滤文件（按扩展名、文件名前缀），减少后续处理的文件数量：

  #"Filtered Target Files" = Table.SelectRows(Source, each [Extension] = ".csv" and Text.StartsWith([Name], "your-file-prefix"))
  

2. 减少动态计算的重复调用 #

• 原代码中Table.ColumnNames(#"Transform File"(#"Sample File"))会在每次扩展列时重新调用转换函数，改为提前定义静态列名列表：

  // 在let开头定义目标列名，替换动态调用
  TargetColumns = {"Business Unit", "Department", "Payroll", "..."}, // 替换为实际需要的列名
  #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", TargetColumns)
  

• 列表lCostingElementClassifications如果数据量大，转成集合提升查找效率（集合的Contains操作比列表快得多）：

  // 在let开头转换为集合并缓冲
  CostingElementSet = List.Buffer(Set.FromList(lCostingElementClassifications)),
  // 后续过滤步骤替换为Set.Contains
  #"Filter Elements" = Table.SelectRows(#"Removed Other Columns", each Set.Contains(CostingElementSet, [Element]))
  

3. 缓存中间结果避免重复计算 #

对数据量较大的中间表使用Table.Buffer()，强制Power Query缓存结果，避免每次操作都重新计算：

• 在数据扩展完成后立即缓冲：

  #"Buffered Raw Data" = Table.Buffer(#"Expanded Table Column1"),
  #"Removed Top Rows" = Table.Skip(#"Buffered Raw Data", 2)
  

• 对参考查询qProjectsML和qRosterML也进行缓冲，确保它们只加载一次：

  // 在let开头缓冲参考查询
  BufferedProjects = Table.Buffer(qProjectsML),
  BufferedRoster = Table.Buffer(qRosterML),
  // 合并查询时使用缓冲后的表
  #"Merged Queries" = Table.NestedJoin(#"Added EFFORT COSTING PROJECT", {"PC NUMBER"}, BufferedProjects, {"PN.TN."}, "qProjectsML", JoinKind.LeftOuter)
  

4. 调整编辑器预览设置 #

• 打开Power Query编辑器，点击「视图」选项卡，勾选「仅显示前1000行预览」，减少每次渲染的数据量
• 关闭「自动预览」功能，手动点击「刷新预览」查看步骤结果，避免每次操作都自动触发预览计算

5. 优化参考查询性能 #

qProjectsML和qRosterML作为关联查询，如果本身存在性能问题，会直接拖累主查询：

• 对这两个查询应用相同的缓存、过滤优化
• 确保它们的数据源是高效的（比如本地文件、已缓冲的表），避免嵌套复杂逻辑

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