Power Query按国家、类别计算最新共有月份标识自定义列求助

let
    Source = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],
    // 新增Category字段的类型转换
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Country", type text}, {"Category", type text}, {"Year", Int64.Type}, {"Month No", Int64.Type}}),

    // 计算每个月的完整日期，保留原逻辑
    #"Added Custom" = Table.AddColumn(#"Changed Type", "fullDate", each #date([Year],[Month No],1),type date),

    // 第一步：统计每个国家的唯一Category总数量
    #"Count Country Categories" = Table.Group(#"Added Custom", {"Country"}, {
        {"All Category Count", each List.Count(List.Distinct([Category])), Int64.Type},
        {"All Rows", each _, type table}
    }),
    #"Expand All Rows" = Table.ExpandTableColumn(#"Count Country Categories", "All Rows", {"Category", "Year", "Month No", "fullDate"}, {"Category", "Year", "Month No", "fullDate"}),

    // 第二步：按国家+月份分组，统计每个月实际覆盖的Category数量
    #"Group By Country Month" = Table.Group(#"Expand All Rows", {"Country", "fullDate"}, {
        {"Month Category Count", each List.Count(List.Distinct([Category])), Int64.Type},
        {"All Category Count", each List.First([All Category Count]), Int64.Type},
        {"Month Rows", each _, type table}
    }),

    // 第三步：筛选出覆盖全量Category的月份，取每个国家的最大月份
    #"Filter Full Coverage Months" = Table.SelectRows(#"Group By Country Month", each [Month Category Count] = [All Category Count]),
    #"Get Latest Common Month" = Table.Group(#"Filter Full Coverage Months", {"Country"}, {{"Latest Common fullDate", each List.Max([fullDate]), type date}}),

    // 第四步：匹配回原数据表打标识位
    #"Merge to Original" = Table.NestedJoin(#"Added Custom", {"Country"}, #"Get Latest Common Month", {"Country"}, "Latest Month Info", JoinKind.LeftOuter),
    #"Expand Latest Date" = Table.ExpandTableColumn(#"Merge to Original", "Latest Month Info", {"Latest Common fullDate"}, {"Latest Common fullDate"}),
    #"Added Latest Flag" = Table.AddColumn(#"Expand Latest Date", "Latest Common Month in a Country", each if [fullDate] = [Latest Common fullDate] then 1 else 0, Int64.Type),
    // 清理冗余字段，如需保留fullDate可删除对应移除规则
    #"Removed Columns" = Table.RemoveColumns(#"Added Latest Flag",{"fullDate", "Latest Common fullDate"})
in
    #"Removed Columns"

• 新增了Category字段的类型适配，确保后续分组统计不会出错
• 先统计每个国家的总Category数量，作为「全品类覆盖」的判断基准
• 按国家+月份维度统计每个月实际存在的Category数量，筛选出和总数量相等的「全品类覆盖月份」
• 从每个国家的全品类覆盖月份中取最大值，就是要求的最新共有月份
• 最后把最新月份匹配回原数据表打1/0标识位，冗余字段已默认清理，可根据需求调整保留字段

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