Power Query数据流粘贴ETL代码报Expression.Error的解决咨询

将一段可正常运行的Power Query ETL代码粘贴到现有数据流的空白查询高级编辑器中创建表时，触发错误：
Expression.Error: The import Transform File matches no exports. Did you miss a module reference?

原代码如下：

let
    Source = SharePoint.Files("https:// ", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "FY24 Forecast-2")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", 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", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Removed Top Rows" = Table.Skip(#"Expanded Table Column1",4),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {" def20230621.xlsx", "Finance Business Partner", "Reporting Location ID", "Reporting Location Description", "Acct Group 2 ID", "Acct Group 2 Desc - Custom", "G/L Account", "G/L Acct Long Text", "Local Currency"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.None), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","FY","20",Replacer.ReplaceText,{"Attribute.1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute.1", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Attribute.2] = "May" then [Attribute.1]-1 else if [Attribute.2] = "Jun" then [Attribute.1]-1 else if [Attribute.2] = "Jul" then [Attribute.1]-1 else if [Attribute.2] = "Aug" then [Attribute.1]-1 else if [Attribute.2] = "Sep" then [Attribute.1]-1 else if [Attribute.2] = "Oct" then [Attribute.1]-1 else if [Attribute.2] = "Nov" then [Attribute.1]-1 else if [Attribute.2] = "Dec" then [Attribute.1]-1 else [Attribute.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Attribute.1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Custom", type text}}, "en-US"),{"Attribute.2", "Custom"},Combiner.CombineTextByDelimiter(" 15 ", QuoteStyle.None),"Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.3", "LC or USD"}, {"Value", "Amount"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Finance Business Partner", type text}, {"G/L Account", type text}, {" def20230621.xlsx", type text}, {"Reporting Location ID", type text}, {"Reporting Location Description", type text}, {"Acct Group 2 ID", type text}, {"Acct Group 2 Desc - Custom", type text}, {"G/L Acct Long Text", type text}, {"Local Currency", type text}, {"LC or USD", type text}, {"Amount", type number}})
in
    #"Changed Type2"

解决方法 #

• 补全依赖的自定义函数：代码中调用的#"Transform File"和#"Sample File"是批量处理文件时自动生成的辅助函数，单独粘贴主查询时这些函数并未被导入。需从原运行环境中导出这两个函数的代码，在当前数据流中先创建空白查询粘贴函数代码，再执行主查询。
• 将文件转换逻辑嵌入主查询：无需依赖外部函数，直接替换#"Transform File"调用为内嵌处理逻辑。例如把each #"Transform File"([Content])替换为：

  each let
      FileContent = Excel.Workbook([Content]),
      TargetSheet = FileContent{[Item="Sheet1",Kind="Sheet"]}[Data], // 替换为你的目标工作表名
      SkippedRows = Table.Skip(TargetSheet,4),
      PromotedHeaders = Table.PromoteHeaders(SkippedRows, [PromoteAllScalars=true])
  in PromotedHeaders
  

  后续的ETL步骤（逆透视、拆分列等）可以保留原逻辑。
• 重新生成批量处理流程：在当前数据流中重新连接SharePoint文件夹，筛选目标文件后，使用Power Query的"从文件夹"功能自动生成Transform File和Sample File函数，再将你的ETL逻辑整合到自动生成的Transform File函数中。

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