Power Query批量处理文件夹内工作簿指定Sheet的技术咨询

问题背景 #

处理3个分销商的Excel文件，每个文件包含SALES、PURCHASING、STOCK及对应Forecast共6类Sheet，还有概览Sheet。已有6个自定义函数（fnSALES、fnPURCHASING等）可单独处理对应Sheet，但批量处理文件夹内所有工作簿指定Sheet时，出现循环引用错误（Expression.Error: A cyclic reference was encountered during evaluation）。

现有代码 #

单个Sheet处理函数（可正常运行） #

(PURCHESES_GROSS_FileName)=>
let
    Source = Excel.Workbook(PURCHESES_GROSS_FileName, null, true),
    #"PURCHASES, GROSS_Sheet" = Source{[Item="PURCHASES, GROSS",Kind="Sheet"]}[Data],
    #"Transposed Table" = Table.Transpose(#"PURCHASES, GROSS_Sheet"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column2"}),
    // 省略中间处理步骤
    #"Inserted Multiplication2" = Table.AddColumn(#"Inserted Multiplication1", "WHSL Value 2023, Eur", each [#"WHSL Price 2023, Eur"] * [Quantity], type number)
in
    #"Inserted Multiplication2"

批量处理尝试代码 #

Buffer查询

let
    Source = Folder.Files("D:\1_1_1_ROOTMARK\Stock Movement\data"),
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx"),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Name", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Content", "Name"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Removed Other Columns", {{"Name", each Text.BeforeDelimiter(_, " SKU Stock movement.xlsx"), type text}}),
    #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"Name", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter1",{{"Name", "Distributor"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each not Text.StartsWith([Distributor], "~")),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Distributor] <> "ELVIM Bal"))
in
    #"Filtered Rows2"

自定义函数（批量用）

(Index) =>
let 
    Inside_Of_workbook = #"Removed Columns"{Index}[data],
    #"_Sheet" = Inside_Of_workbook{[Item=[Item],Kind="Sheet"]}[Data],
    #"Transposed Table" = Table.Transpose(#"_Sheet"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column3", "Column2"}),
    // 省略中间处理步骤
    #"Inserted Multiplication2" = Table.AddColumn(#"Inserted Multiplication1", "WHSL Value 2023, Eur", each [#"WHSL Price 2023, Eur"] * [Quantity], type number)
in
    #"Inserted Multiplication2"

主查询

let
    Buffer_Hopes_For_All_Sheet_fn = Buffer,
    WorkbookContents = Table.AddColumn(Buffer_Hopes_For_All_Sheet_fn, "Sheets", each Excel.Workbook([Content])),
    #"Added Index" = Table.AddIndexColumn(WorkbookContents, "Index", 0, 1, Int64.Type),
    Sheets = Table.AddColumn(#"Added Index", "data", each #"Added Index"{[Index]}[Sheets]),
    #"Removed Columns" = Table.RemoveColumns(Sheets,{"Content", "Sheets"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "Clean_data", each #"fn_PURCHASES, GROSS (Foreccast)"([Index])),
    Clean_data = #"Invoked Custom Function"{0}[Clean_data]
in
    Clean_data

Sheet结构示例 #

PURCHASES: #

SALES: #

STOCK: #

解决方案 #

问题根源 #

循环引用来自主查询中Sheets = Table.AddColumn(#"Added Index", "data", each #"Added Index"{[Index]}[Sheets])这一步：在创建data列时，引用了正在构建的#"Added Index"表（包含未完全生成的data列），导致Power Query在求值时陷入循环。

修正方案 #

1. 重构自定义函数

将自定义函数改为直接接收工作簿内容和Sheet名称作为参数，避免通过索引跨查询引用：

(WorkbookContent as binary, SheetName as text, DistributorName as text) =>
let
    Source = Excel.Workbook(WorkbookContent, null, true),
    TargetSheet = Source{[Item=SheetName, Kind="Sheet"]}[Data],
    // 复用原单个Sheet处理的逻辑
    #"Transposed Table" = Table.Transpose(TargetSheet),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column2"}),
    // 省略中间处理步骤
    #"Inserted Multiplication2" = Table.AddColumn(#"Inserted Multiplication1", "WHSL Value 2023, Eur", each [#"WHSL Price 2023, Eur"] * [Quantity], type number),
    // 添加分销商标识列，方便后续区分数据
    #"Added Distributor" = Table.AddColumn(#"Inserted Multiplication2", "Distributor", each DistributorName)
in
    #"Added Distributor"

2. 重构主查询

直接在主查询中调用修改后的自定义函数，避免索引引用和循环：

let
    // 加载文件夹内的Excel文件
    Source = Folder.Files("D:\1_1_1_ROOTMARK\Stock Movement\data"),
    // 过滤有效文件
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~") and [Name] <> "ELVIM Bal SKU Stock movement.xlsx"),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Name", Order.Ascending}}),
    // 提取分销商名称
    #"Extracted Distributor" = Table.TransformColumns(#"Sorted Rows", {{"Name", each Text.BeforeDelimiter(Text.BeforeDelimiter(_, " SKU Stock movement.xlsx"), " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Distributor",{{"Name", "Distributor"}}),
    // 保留必要列
    #"Kept Columns" = Table.SelectColumns(#"Renamed Columns",{"Content", "Distributor"}),
    // 批量调用自定义函数处理指定Sheet（示例为PURCHASES, GROSS）
    #"Processed PURCHASES" = Table.AddColumn(#"Kept Columns", "Cleaned PURCHASES", each fnPURCHASING([Content], "PURCHASES, GROSS", [Distributor])),
    // 展开处理后的表
    #"Expanded PURCHASES" = Table.ExpandTableColumn(#"Processed PURCHASES", "Cleaned PURCHASES", Table.ColumnNames(#"Processed PURCHASES"{0}[Cleaned PURCHASES])),
    // 如需处理多个Sheet，重复上述AddColumn+Expand步骤即可
    // #"Processed SALES" = Table.AddColumn(#"Expanded PURCHASES", "Cleaned SALES", each fnSALES([Content], "SALES", [Distributor])),
    // #"Expanded SALES" = Table.ExpandTableColumn(#"Processed SALES", "Cleaned SALES", Table.ColumnNames(#"Processed SALES"{0}[Cleaned SALES]))
in
    #"Expanded PURCHASES"

3. 批量处理多个Sheet的优化

如果需要一次性处理所有6类Sheet，可以创建一个包含所有目标Sheet名称的列表，通过List.Accumulate循环处理：

let
    // 加载文件夹内的Excel文件
    Source = Folder.Files("D:\1_1_1_ROOTMARK\Stock Movement\data"),
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~") and [Name] <> "ELVIM Bal SKU Stock movement.xlsx"),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Name", Order.Ascending}}),
    #"Extracted Distributor" = Table.TransformColumns(#"Sorted Rows", {{"Name", each Text.BeforeDelimiter(Text.BeforeDelimiter(_, " SKU Stock movement.xlsx"), " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Distributor",{{"Name", "Distributor"}}),
    #"Kept Columns" = Table.SelectColumns(#"Renamed Columns",{"Content", "Distributor"}),
    // 定义目标Sheet列表及对应处理函数
    TargetSheets = {
        {"SALES", fnSALES},
        {"PURCHASING", fnPURCHASING},
        {"STOCK", fnSTOCK},
        {"SALES(Forecast)", fnSALESForecast},
        {"PURCHASING(Forecast)", fnPURCHASINGForecast},
        {"STOCK(Forecast)", fnSTOCKForecast}
    },
    // 循环处理所有Sheet
    #"Processed All Sheets" = List.Accumulate(TargetSheets, #"Kept Columns", (state, current) =>
        let
            SheetName = current{0},
            ProcessFn = current{1},
            #"Added Processed Column" = Table.AddColumn(state, "Cleaned " & SheetName, each ProcessFn([Content], SheetName, [Distributor])),
            #"Expanded Column" = Table.ExpandTableColumn(#"Added Processed Column", "Cleaned " & SheetName, Table.ColumnNames(#"Added Processed Column"{0}[Cleaned " & SheetName & "]))
        in
            #"Expanded Column"
    )
in
    #"Processed All Sheets"

关键注意事项 #

• 所有自定义函数参数统一为WorkbookContent、SheetName和DistributorName，避免依赖外部查询变量。
• 处理后添加Distributor列，方便后续区分不同分销商的数据。
• 展开表时使用Table.ColumnNames动态获取列名，避免硬编码。

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