Power Apps中筛选SharePoint列表：替换同时间Shift Overwrite为y的记录

原始SharePoint列表数据 #

Date                Time          Name                 Shift Overwrite
=======             ====        ===========            ===============
09/01/2022          04:00       White Chessman         n
09/01/2022          04:00       Kim Lee                y
09/01/2022          20:00       Tom Shoes              n
09/02/2022          04:00       John Smith             y
09/02/2022          04:00       Kim Lee                n
09/02/2022          20:00       White Chessman         n
09/03/2022          04:00       Kim Lee                n

期望筛选结果 #

Date                Time          Name                 Shift Overwrite
=======             ====        ===========            ===============
09/01/2022          04:00       Kim Lee                y
09/01/2022          20:00       Tom Shoes              n
09/02/2022          04:00       John Smith             y
09/02/2022          20:00       White Chessman         n
09/03/2022          04:00       Kim Lee                n

解决方案 #

核心逻辑是优先保留同日期时间下Shift Overwrite为"y"的记录，无对应"y"记录时保留"n"的记录，以下两种方法均可实现：

方法1：合并筛选结果

先提取所有"y"的记录，再筛选出没有对应"y"记录的"n"记录，最后合并两者：

Collect(
    FilteredShiftRecords,
    // 获取所有Shift Overwrite为"y"的记录
    Filter(YourSharePointList, 'Shift Overwrite' = "y"),
    // 获取无对应"y"记录的"n"记录
    Filter(
        YourSharePointList,
        'Shift Overwrite' = "n",
        !Exists(
            Filter(YourSharePointList, 'Shift Overwrite' = "y"),
            Date = ThisRecord.Date && Time = ThisRecord.Time
        )
    )
)

方法2：分组后取优先级记录

按Date和Time分组，每组内优先选择"y"的记录，无"y"则选"n"的记录：

Collect(
    FilteredShiftRecords,
    ForAll(
        GroupBy(YourSharePointList, "Date", "Time", "GroupedRecords"),
        If(
            CountRows(Filter(GroupedRecords, 'Shift Overwrite' = "y")) > 0,
            First(Filter(GroupedRecords, 'Shift Overwrite' = "y")),
            First(Filter(GroupedRecords, 'Shift Overwrite' = "n"))
        )
    )
)

使用时将YourSharePointList替换为你实际的SharePoint列表名称，FilteredShiftRecords是存储筛选结果的集合，后续可直接在控件中引用这个集合展示数据。

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