Power Query：按分组列的MIN/MAX值移除对应首尾行

• 数据场景：多区域商品销售数据，每个区域下每日有多条销售记录
• 需求：按区域移除**最早（DATE列最小值）和最晚（DATE列最大值）**日期的所有行，仅保留中间日期的记录。例如North区域需移除Fri 09 Dec 22和Mon 12 Dec 22的所有行
• 当前困境：能移除全局日期的MIN/MAX行，但无法按区域实现；尝试分组标记各区域MIN/MAX日期时，Product和Amount列变为列表，展开后产生大量重复行
• 现有Power Query代码：

Source = Excel.Workbook(File.Contents("C:\Temp\Test Data\2022 Sales.xlsx"), null, true),
Test_Sheet = Source{[Item="Test",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Test_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Region", type text}, {"Date", type date}, {"Time", type datetime}, {"Product", type text}, {"Amount", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Region"}, {
    {"Date", each [Date]},
    {"Product", each [Product]},
    {"Amount", each [Amount]},
    {"DontKeepMinFlag", each Table.First(_)[Date]},
    {"DontKeepMaxFlag", each Table.Last(_)[Date]}}),
#"Expanded Date" = Table.ExpandListColumn(#"Grouped Rows", "Date"),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Date", "Custom", each if [DontKeepMinFlag] = [Date] then true else if [DontKeepMaxFlag] = [Date] then true else false),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = false)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"DontKeepMinFlag", "DontKeepMaxFlag", "Custom"})

in
    #"Removed Columns"

修改后的Power Query代码如下，核心思路是先分组计算各区域的日期范围，再回原表匹配过滤，避免列表展开导致的重复：

Source = Excel.Workbook(File.Contents("C:\Temp\Test Data\2022 Sales.xlsx"), null, true),
Test_Sheet = Source{[Item="Test",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Test_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Region", type text}, {"Date", type date}, {"Time", type datetime}, {"Product", type text}, {"Amount", Int64.Type}}),
// 分组计算每个区域的最小、最大日期
#"Grouped for Date Range" = Table.Group(#"Changed Type", {"Region"}, {
    {"MinDate", each List.Min([Date])},
    {"MaxDate", each List.Max([Date])}
}),
// 将日期范围合并回原表
#"Merged with Date Range" = Table.NestedJoin(#"Changed Type", {"Region"}, #"Grouped for Date Range", {"Region"}, "DateRange", JoinKind.LeftOuter),
#"Expanded DateRange" = Table.ExpandTableColumn(#"Merged with Date Range", "DateRange", {"MinDate", "MaxDate"}, {"MinDate", "MaxDate"}),
// 过滤掉等于最小或最大日期的行
#"Filtered Rows" = Table.SelectRows(#"Expanded DateRange", each [Date] <> [MinDate] and [Date] <> [MaxDate]),
// 移除辅助列
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MinDate", "MaxDate"})
in
    #"Removed Columns"

关键步骤说明 #

• 分组仅计算每个区域的MinDate和MaxDate，不处理其他列，避免生成列表结构
• 通过左连接将区域的日期范围合并回原表，完整保留所有原始行的结构和数据
• 直接在原表中过滤掉日期等于区域最小/最大日期的行，无需展开列表，彻底避免重复行问题

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