Power Query添加Table.Buffer优化List.PositionOf性能失败求解决

报错原因 #

你遇到的Expression.Error: 2 arguments were passed to a function which expects 1错误，是因为Table.Buffer只接受1个参数（要缓存的表），你大概率在尝试添加缓存时错误传入了多余参数（比如误写成Table.Buffer(#"Ledger Account", [Legacy ID])这类形式）。

性能优化方案（替代低效的逐行查找） #

你的原始代码通过List.PositionOf逐行查找索引，再多次按索引取列，本质是循环遍历查找表，数据量大时必然卡顿。更高效的做法是直接用表合并或字典映射替代，同时正确使用Table.Buffer缓存查找表。

方案1：使用Table.Join合并表（最直观，推荐） #

直接将Table1和Ledger Account通过People Soft_FIN_ACCT和Legacy ID关联，一步获取所有需要的列，完全避开循环查找：

let
    // 缓存查找表到内存，避免重复读取
    BufferedLedger = Table.Buffer(#"Ledger Account"),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROD_CD", type text}, {"People Soft_FIN_ACCT", type text}}),
    // 直接合并两个表，关联字段为People Soft_FIN_ACCT和Legacy ID
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"People Soft_FIN_ACCT"}, BufferedLedger, {"Legacy ID"}, "Ledger Account", JoinKind.LeftOuter),
    // 展开需要的列
    #"Expanded Ledger Account" = Table.ExpandTableColumn(#"Merged Queries", "Ledger Account", {"Ledger Account Identifier", "Ledger Account Name", "SC Ref ID", "Bank Account", "Distribution Partner", "Classification", "Affiliate"}, {"WorkDay Account Number", "Workday Account Name", "Spend Category", "Bank Account", "Distribution Partner", "Classification", "Affiliate"})
in
    #"Expanded Ledger Account"

方案2：使用字典映射（适合需要自定义逻辑的场景） #

先将查找表转换成以Legacy ID为键的记录字典，之后直接通过键快速取值：

let
    // 缓存并转换查找表为字典：Key=Legacy ID，Value=整行记录
    LedgerDict = Table.Buffer(#"Ledger Account")
                |> Table.ToRecords
                |> List.ToTable
                |> Table.RenameColumns({"Column1", "Column2"}, {"Legacy ID", "Record"})
                |> Table.ToDictionary([Legacy ID], each [Record]),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROD_CD", type text}, {"People Soft_FIN_ACCT", type text}}),
    // 一次性添加所有需要的列，通过字典直接取值
    #"Added Custom Columns" = Table.AddColumn(#"Changed Type", "LookupRecord", each try LedgerDict{[People Soft_FIN_ACCT]} otherwise null),
    #"Extracted Values" = Table.ExpandRecordColumn(#"Added Custom Columns", "LookupRecord", {"Ledger Account Identifier", "Ledger Account Name", "SC Ref ID", "Bank Account", "Distribution Partner", "Classification", "Affiliate"}, {"WorkDay Account Number", "Workday Account Name", "Spend Category", "Bank Account", "Distribution Partner", "Classification", "Affiliate"})
in
    #"Extracted Values"

关键优化点 #

• Table.Buffer的正确用法：只需要传入要缓存的表（比如Table.Buffer(#"Ledger Account")），将查找表加载到内存，避免每次查找都重新读取源数据。
• 避免逐行循环：原始代码的List.PositionOf+多次Table.AddColumn是O(n*m)的时间复杂度，合并表或字典映射是O(n)的复杂度，数据量大时性能差距极大。

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