Power Query中List.Buffer/List.PositionOf加载异常及合并报错问题

问题分析 #

方法1（List.PositionOf）的性能问题 #

你用List.PositionOf逐行匹配索引的方式，即使加了List.Buffer，但存在两个核心问题：

• 重复对单个值（比如[Location]、[Location Position]）调用List.Buffer完全无效，Buffer应该作用于整个列表（比如Sheet1[Store]），且只需要缓存一次
• 逐行遍历列表的时间复杂度是O(n*m)，当源数据量大（100MB CSV）时，会导致计算量爆炸，出现加载无限、数据膨胀的情况

方法2（合并查询）的格式错误 #

合并后出现Data.Format Error - can't convert to number，原因是关联列类型不匹配：你的代码中把Location列设为type number，但Sheet1[Store]列可能是文本类型（或反之），Power Query在合并时强制类型转换失败。

推荐解决方案：优化合并查询方法 #

直接用Power Query的合并查询功能（本质是数据库式关联），这是处理大数据量匹配的最优方案，时间复杂度远低于逐行遍历。以下是修正后的完整代码：

let
    // 提前缓存关联表，避免重复读取
    Buffered_Sheet1 = Table.Buffer(Sheet1),
    Buffered_Sheet2 = Table.Buffer(Sheet2),

    // 源数据加载流程（保留原有逻辑）
    Source = Folder.Files("G:\Trintech\Trintech Export Files\Trintech Daily Bank Exports"),
    #"Filtered Rows1" = Table.SelectRows(Source, each ([Extension] = ".csv")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
    #"Filtered Hidden Files2" = Table.SelectRows(#"Filtered Hidden Files1", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files2", "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"))),
    
    // 关键修改：统一关联列类型为文本（避免数字/文本不匹配）
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{
        {"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type number},
        // 把Location设为文本，和Sheet1[Store]类型统一
        {"Column5", type text}, {"Column6", type date}, {"Column7", type date}, {"Column8", Int64.Type}, {"Column9", type text},
        {"Column10", type number}, {"Column11", type date}, {"Column12", type number}, {"Column13", type text}, {"Column14", type text},
        {"Column15", type text}, {"Column16", type any}, {"Column17", type number}, {"Column18", type any}, {"Column19", type any},
        {"Column20", type any}, {"Column21", type text}, {"Column22", type any}, {"Column23", type any}
    }),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Internal/External"}, {"Column2", "DR/CR"}, {"Column3", "Transaction Type"}, {"Column4", "Amount"}, {"Column5", "Location"}, {"Column6", "Post Date"}, {"Column7", "Effective Date"}, {"Column8", "Account"}, {"Column9", "Account Number"}, {"Column10", "Bai Code"}, {"Column11", "Match Date"}, {"Column12", "Match Group ID"}, {"Column13", "Match Rule"}, {"Column14", "Match Technique"}, {"Column15", "Matched/Unmatched"}, {"Column16", "Order ID"}, {"Column17", "Reference 10"}, {"Column18", "Reference 5"}, {"Column19", "Reference 6"}, {"Column20", "Reference 7"}, {"Column21", "Reference 8"}, {"Column22", "Reference 9"}, {"Column23", "Reference Number"}}),
    
    // 第一步：合并Sheet1，通过Location匹配Store获取Region
    #"Merged with Sheet1" = Table.NestedJoin(#"Renamed Columns", {"Location"}, Buffered_Sheet1, {"Store"}, "Sheet1_Data", JoinKind.LeftOuter),
    #"Expanded Sheet1" = Table.ExpandTableColumn(#"Merged with Sheet1", "Sheet1_Data", {"Region"}, {"Region"}),
    
    // 第二步：合并Sheet2，通过Region匹配获取Specialist
    #"Merged with Sheet2" = Table.NestedJoin(#"Expanded Sheet1", {"Region"}, Buffered_Sheet2, {"Region"}, "Sheet2_Data", JoinKind.LeftOuter),
    #"Expanded Sheet2" = Table.ExpandTableColumn(#"Merged with Sheet2", "Sheet2_Data", {"Specialist"}, {"Specialist"}),
    
    // 处理匹配失败的空值（可选）
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Expanded Sheet2", {{"Region", ""}, {"Specialist", ""}})
in
    #"Replaced Errors"

关键修改说明 #

1. 提前缓存关联表：用Table.Buffer缓存Sheet1和Sheet2，避免Power Query重复读取这两张表，提升性能
2. 统一关联列类型：把Location列从type number改为type text，确保和Sheet1[Store]类型一致（如果Sheet1[Store]是数字，就把Location保留为数字，根据实际情况调整）
3. 分步合并查询：先合并Sheet1获取Region，再用Region合并Sheet2获取Specialist，逻辑清晰且避免嵌套错误
4. 移除无效的List.PositionOf逻辑：完全替换为更高效的合并查询，解决性能问题

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