如何在分组数据中基于Source.Name列值生成递增索引列

你当前的代码会给子表每行递增索引，要实现仅当Source.Name值变化时索引才递增（相同Source.Name的行共用同一索引），可以修改Added Custom步骤的逻辑，通过子表内再分组的方式实现：

修改后的完整代码片段 #

#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Source.Name", type text}, {"Team", type text}, {"Project category", type text}, {"Project type", type text}, {"Role", type text}, {"Month", type text}, {"Value", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category", "Month"}, {{"Count", each _, type table [Source.Name=nullable text, Team=nullable text, Project category=nullable text, Project type=nullable text, Role=nullable text, Month=nullable text, Value=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
    let
        // 子表内按Source.Name分组，保留每组数据
        GroupedByName = Table.Group([Count], {"Source.Name"}, {{"GroupData", each _, type table}}),
        // 给每个Source.Name分组分配递增索引
        AddedIndex = Table.AddIndexColumn(GroupedByName, "#FCST", 1, 1),
        // 展开分组，让同一Source.Name的行共享索引
        ExpandedGroup = Table.ExpandTableColumn(AddedIndex, "GroupData", {"Team", "Project category", "Project type", "Role", "Month", "Value"}, {"Team", "Project category", "Project type", "Role", "Month", "Value"})
    in
        ExpandedGroup
),
// 展开外层Custom列，得到最终结果
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Source.Name", "Team", "Project category", "Project type", "Role", "Month", "Value", "#FCST"}, {"Source.Name", "Team", "Project category", "Project type", "Role", "Month", "Value", "#FCST"})

关键逻辑说明 #

1. 子表内分组：在每个Team+Project category+Month的分组子表里，先按Source.Name再次分组，把相同来源的行归为一组。
2. 分配分组索引：给每个Source.Name分组添加从1开始的递增索引，这样同一个来源的所有行就会拥有同一个#FCST值。
3. 展开数据：将分组后的表格展开，把索引值映射回每一行，最后再展开外层的分组列，得到完整的带目标索引的表格。

注意事项 #

如果需要保证行顺序和原表一致，建议在Changed Type步骤后添加排序：

#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Team", Order.Ascending}, {"Project category", Order.Ascending}, {"Month", Order.Ascending}, {"Source.Name", Order.Ascending}}),

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