在Excel Power Query中模拟MINIFS公式的问题求助

原始数据 #

match_id, minute, value
1, 1, 5
1, 2, 10
1, 3, 15
1, 4, -5
1, 5, -10
1, 6, -15
2, 1, 10
2, 2, 15
2, 3, 20
2, 4, -2

问题描述 #

我在Excel Power Query里尝试添加自定义列，用公式 List.Min(Table.SelectRows(#"Changed Type", each ([match_id]=[match_id] and [value] <= 0))[value]) 模拟Excel的MINIFS函数，但结果不对——本该按当前行的match_id筛选后取最小值，结果返回的是全表的最小值。单独用Table.SelectRows能得到正确的筛选行列表，但套上List.Min就出问题。

期望结果 #

match_id, minute, value, minifs
1, 1, 5, -15
1, 2, 10, -15
1, 3, 15, -15
1, 4, -5, -15
1, 5, -10, -15
1, 6, -15, -15
2, 1, 10, -2
2, 2, 15, -2
2, 3, 20, -2
2, 4, -2, -2

问题原因 #

问题出在变量作用域上。在Table.SelectRows的each表达式里，[match_id]会优先引用筛选上下文里的列值，而不是当前行的match_id。相当于你写的[match_id]=[match_id]永远为真，筛选条件就只剩[value] <= 0，所以List.Min取的是全表所有负值的最小值。

解决方法 #

方法1：提前保存当前行的match_id #

在自定义列里先把当前行的match_id存到变量里，再用这个变量做筛选：

let
    currentMatch = [match_id],
    filteredRows = Table.SelectRows(#"Changed Type", each ([match_id] = currentMatch and [value] <= 0)),
    minValue = List.Min(filteredRows[value])
in
    minValue

方法2：分组后合并（更高效） #

如果数据量较大，分组的方法性能更好：

1. 先对原表按match_id分组，计算每组中value<=0的最小值：

let
    grouped = Table.Group(#"Changed Type", {"match_id"}, {{"minifs", each List.Min(List.Select([value], (x)=>x<=0)), type number}})
in
    grouped

2. 再将分组后的表和原表用match_id做合并，把minifs列添加到原表中。

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