如何在Power Query中复刻Excel的DOC ID计数编号公式？

问题需求 #

要在Power Query中复刻以下Excel公式，为每个DOC ID生成形如DOC ID-1、DOC ID-2的递增编号：

=[@[DOC ID]] & "-" & COUNTIF([DOC ID],[@[DOC ID]])

现有Power Query代码 #

let
    Source = ReqProcBudget2024,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Doc Type] = "PO: ")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,"",Replacer.ReplaceValue,{"Line\Sched\Dist", "Vendor"}),
    #"Merged Queries" = Table.NestedJoin(#"Replaced Value", {"Budg Dt"}, FY_Dates, {"Date"}, "FY_Dates", JoinKind.LeftOuter),
    #"Expanded FY_Dates" = Table.ExpandTableColumn(#"Merged Queries", "FY_Dates", {"Index"}, {"Index"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded FY_Dates", {"DOC ID", "Line\Sched\Dist", "Vendor", "Original Amount", "Account", "Department", "Fund"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Show COVID Related POs?", each if Text.Contains([PO Ref], "COVID",Comparer.OrdinalIgnoreCase) then "YES" else "NO"),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "PO Original Amount by Line Item", each if [Original Amount] >= 50000 then "$50k or MORE" else "LESS than $50k"),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "PO Remaining Amount by Line Item", each if [Remaining Amount] >= 50000 then "$50k or MORE" else "LESS than $50k"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Show COVID Related POs?", type text}, {"PO Original Amount by Line Item", type text}, {"PO Remaining Amount by Line Item", type text}})
in
    #"Changed Type"

尝试的错误代码 #

以下自定义列代码生成的结果为固定编号（如DOC ID-9481），无法实现递增：

= Table.AddColumn(#"Changed Type", "UI", each [DOC ID] & "-" & Text.From(Table.RowCount(Table.SelectRows(#"Changed Type", each [DOC ID] = _[DOC ID]))))

错误原因 #

该代码用Table.RowCount统计的是整个表中当前DOC ID的总行数，而非当前行在同DOC ID组内的序号，因此所有行返回相同数字。

正确解决方案 #

通过分组+组内索引的方式实现递增编号，在现有代码的Changed Type步骤后添加以下步骤：

#"Grouped by DOC ID" = Table.Group(#"Changed Type", {"DOC ID"}, {{"GroupedData", each Table.AddIndexColumn(_, "GroupIndex", 1, 1), type table}}),
#"Expanded Grouped Data" = Table.ExpandTableColumn(#"Grouped by DOC ID", "GroupedData", 
    {"Line\Sched\Dist", "Vendor", "Original Amount", "Account", "Department", "Fund", 
     "Show COVID Related POs?", "PO Original Amount by Line Item", "PO Remaining Amount by Line Item", "GroupIndex"}, 
    {"Line\Sched\Dist", "Vendor", "Original Amount", "Account", "Department", "Fund", 
     "Show COVID Related POs?", "PO Original Amount by Line Item", "PO Remaining Amount by Line Item", "GroupIndex"}),
#"Added UI Column" = Table.AddColumn(#"Expanded Grouped Data", "UI", each [DOC ID] & "-" & Text.From([GroupIndex])),
#"Removed Group Index" = Table.RemoveColumns(#"Added UI Column",{"GroupIndex"})

完整代码 #

let
    Source = ReqProcBudget2024,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Doc Type] = "PO: ")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,"",Replacer.ReplaceValue,{"Line\Sched\Dist", "Vendor"}),
    #"Merged Queries" = Table.NestedJoin(#"Replaced Value", {"Budg Dt"}, FY_Dates, {"Date"}, "FY_Dates", JoinKind.LeftOuter),
    #"Expanded FY_Dates" = Table.ExpandTableColumn(#"Merged Queries", "FY_Dates", {"Index"}, {"Index"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded FY_Dates", {"DOC ID", "Line\Sched\Dist", "Vendor", "Original Amount", "Account", "Department", "Fund"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Show COVID Related POs?", each if Text.Contains([PO Ref], "COVID",Comparer.OrdinalIgnoreCase) then "YES" else "NO"),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "PO Original Amount by Line Item", each if [Original Amount] >= 50000 then "$50k or MORE" else "LESS than $50k"),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "PO Remaining Amount by Line Item", each if [Remaining Amount] >= 50000 then "$50k or MORE" else "LESS than $50k"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Show COVID Related POs?", type text}, {"PO Original Amount by Line Item", type text}, {"PO Remaining Amount by Line Item", type text}}),
    #"Grouped by DOC ID" = Table.Group(#"Changed Type", {"DOC ID"}, {{"GroupedData", each Table.AddIndexColumn(_, "GroupIndex", 1, 1), type table}}),
    #"Expanded Grouped Data" = Table.ExpandTableColumn(#"Grouped by DOC ID", "GroupedData", 
        {"Line\Sched\Dist", "Vendor", "Original Amount", "Account", "Department", "Fund", 
         "Show COVID Related POs?", "PO Original Amount by Line Item", "PO Remaining Amount by Line Item", "GroupIndex"}, 
        {"Line\Sched\Dist", "Vendor", "Original Amount", "Account", "Department", "Fund", 
         "Show COVID Related POs?", "PO Original Amount by Line Item", "PO Remaining Amount by Line Item", "GroupIndex"}),
    #"Added UI Column" = Table.AddColumn(#"Expanded Grouped Data", "UI", each [DOC ID] & "-" & Text.From([GroupIndex])),
    #"Removed Group Index" = Table.RemoveColumns(#"Added UI Column",{"GroupIndex"})
in
    #"Removed Group Index"

步骤说明 #

1. 分组：按DOC ID分组，将每组数据保留为子表
2. 组内加索引：给每个子表添加从1开始的递增索引GroupIndex
3. 展开子表：将分组后的子表展开，恢复原有列和新的索引列
4. 生成UI列：用DOC ID拼接GroupIndex得到目标编号
5. 清理列：移除临时的索引列（可选）

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