Power Query M语言：条件计数的奇技淫巧，告别COUNTIFS的烦恼！
Power Query M语言：条件计数的奇技淫巧，告别COUNTIFS的烦恼！
谁说Power Query里不能像Excel一样用COUNTIFS？只不过需要一点点M语言的魔法！默认路径？不存在的！我们“PQ老顽童”今天就来点不一样的！
想象一下，你要统计一个包含数百万行数据的交易记录表中，特定时间段内，特定商品的交易次数，而且这个时间段还是动态变化的。用Excel？别闹了，你会崩溃的！但用M语言，一切都变得优雅而高效。准备好迎接这场M语言的探险之旅了吗？
M语言条件计数：不止一种姿势！
网络上充斥着Table.SelectRows 和 Table.RowCount 的组合用法，没错，它们能用。但我们今天追求的是更灵活、更高效的解决方案。让我们深入M语言，解锁条件计数的多种姿势！
List.Count + Table.Column：更胜一筹的组合
Table.SelectRows 在处理大数据量时，性能可能会成为瓶颈。这时，List.Count 函数结合 Table.Column 函数的优势就显现出来了。Table.Column 可以将表中的某一列数据提取成一个列表，然后 List.Count 就可以对这个列表进行条件计数，效率更高！
举个例子，假设我们有一个名为 Transactions 的表，包含 Date（日期）和 Product（商品）两列。我们要统计2026年1月1日至2026年1月31日之间，商品名为“苹果”的交易次数。
首先，我们创建一个自定义函数：
(TableName as table, DateColumn as text, ProductColumn as text, StartDate as date, EndDate as date, ProductName as text) =>
let
Source = TableName,
DateList = Table.Column(Source, DateColumn),
ProductList = Table.Column(Source, ProductColumn),
CombinedList = List.Zip({DateList, ProductList}),
FilteredList = List.Select(
CombinedList,
each Date.IsInInterval( {_{0}, Date.AddDays(_{0}, 1)}, {StartDate, Date.AddDays(EndDate,1)}) and _{1} = ProductName
),
Count = List.Count(FilteredList)
in
Count
这个函数接受表名、日期列名、商品列名、开始日期、结束日期和商品名称作为参数，返回满足条件的交易次数。调用方法如下：
= fx_ConditionalCount(Transactions, "Date", "Product", #date(2026, 1, 1), #date(2026, 1, 31), "苹果")
代码解释：
Table.Column(Source, DateColumn)和Table.Column(Source, ProductColumn)分别提取日期列和商品列的数据到列表。List.Zip({DateList, ProductList})将两个列表合并为一个列表，每个元素是一个包含日期和商品名称的列表。List.Select函数用于筛选满足条件的元素。Date.IsInInterval( {_{0}, Date.AddDays(_{0}, 1)}, {StartDate, Date.AddDays(EndDate,1)})判断日期是否在指定的时间段内。_{1} = ProductName判断商品名称是否与指定的商品名称相等。List.Count函数用于计算筛选后的列表的元素个数。
M语言的乐高精神：自由组合，创造无限可能！
M语言的强大之处在于其灵活性。你可以将不同的M函数组合起来，创造出更强大的功能。例如，可以将条件计数与分组聚合结合起来，统计每个组内满足特定条件的行数。假设我们要统计每个商品类别中，销售额大于1000的订单数量。
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXKSc1JRMjQwMDA0MjDUUTI0ABbGFEMpShkZGBgaCAA=", BinaryEncoding.Base64), Compression.Deflate)), type table [Category=text, Sales=number]),
Grouped = Table.Group(Source, {"Category"}, {{"Count", each List.Count(List.Select([Sales], (s) => s > 1000)), type number}})
in
Grouped
这段代码首先使用 Table.Group 函数按照 Category 列进行分组，然后使用 List.Count 和 List.Select 函数统计每个组内 Sales 大于 1000 的订单数量。
| Category | Count |
|---|---|
| A | 1 |
| B | 0 |
性能优化小贴士
- 尽量避免在
Table.SelectRows中使用复杂的表达式。可以将复杂的表达式拆分成多个步骤，先计算出中间结果，再进行筛选。 - 对于大型数据集，可以考虑使用索引来提高查询速度。
- 定期清理不必要的中间步骤，减少内存占用。
超越计数：探索条件聚合的无限可能
既然我们已经学会了条件计数，为什么不更进一步，实现条件求和、条件平均值等更高级的聚合操作呢？List.Sum、List.Average 等函数就是我们的好帮手。
例如，计算特定客户的平均订单金额：
(TableName as table, CustomerColumn as text, SalesColumn as text, CustomerName as text) =>
let
Source = TableName,
FilteredTable = Table.SelectRows(Source, each Record.Field(_, CustomerColumn) = CustomerName),
SalesList = Table.Column(FilteredTable, SalesColumn),
AverageSales = List.Average(SalesList)
in
AverageSales
M语言调试：像侦探一样破案！
调试M代码就像侦探破案，你需要仔细观察每一个线索，最终找到隐藏的Bug凶手！Power Query的调试功能可以帮助你查看查询步骤的中间结果，定位错误发生的位置。
常见错误类型：
- 类型转换错误： 确保你的数据类型正确。例如，如果你的日期列是文本类型，你需要先将其转换为日期类型才能进行日期比较。
- 逻辑错误： 检查你的条件判断是否正确。可以使用
try...otherwise语句来捕获错误，并返回更有意义的错误信息。 - 性能问题： 如果查询速度过慢，可以使用 Power Query 的性能分析工具来分析瓶颈。
展望未来：M语言的无限可能
2026年，M语言的语法和功能将会不断完善和扩展。Power Query将会与其他数据分析工具（例如，Power BI、Azure Data Factory）更紧密地集成。M语言将会成为数据分析师必备的技能之一。
所以，不要犹豫了，赶紧加入M语言的学习行列，一起探索数据分析的无限可能吧！记住，M语言不仅仅是一种工具，更是一种思维方式，一种解决问题的艺术！
友情链接：
* PowerQuery M | Microsoft Learn：深入了解M语言的官方文档。
* M函数参考：查阅M函数的详细用法。
* Power Query M函数：知乎专栏，学习M函数的进阶技巧。