PowerQuery COUNTIF以前的日期

本文介绍了PowerQuery COUNTIF以前的日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我对PowerQuery有点生疏。 我需要在同一张表中计算上一个条目。

I'm a little rusty on PowerQuery. I need to count "previous" entries in the same table.

例如，假设我们有一个汽车销售表。 就PowerQuery而言，该表将命名为 tblCarSales

For example, let's say we have a table of car sales. For the purposes of PowerQuery, this table will be named tblCarSales

我需要添加两个汇总列。

第一个汇总列是以前的销售计数。 Excel公式为 = COUNTIF（[销售日期] ，<& [@ [销售日期]]）

The first aggregate column is the count of previous sales. The Excel formula would be =COUNTIF([Sale Date],"<"&[@[Sale Date]])

第二个汇总列是 make 的先前销售额计数。 Excel式将为 = COUNTIFS（[销售日期]，<& [@ [销售日期]]，[制造]，[@制造]）

如何在PowerQuery中完成此行为，而不是使用Excel公式？ 例如，我从源语句开始：

How can this behavior be accomplished in PowerQuery, instead of using Excel formulas? For example, I'm starting with the source statement:

let Source = Excel.CurrentWorkbook(){[Name="tblCarSales"]}[Content] in Source

...，其中源表仅提供 Make ， Model 和销售日期列。

... where the source table only provides the Make, Model, and Sale Date columns.

推荐答案

您可以使用以下方法进行操作列表和表功能。我会同时显示两者。

You can do this sort of thing using List and Table functions. I'll show both.

let Source = Excel.CurrentWorkbook(){[Name="tblCarSales"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Previous Sale Count", (C) => List.Count(List.Select(Source[Sale Date], each _ < C[Sale Date]))), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Previous Sale Count By Make", (C) => Table.RowCount(Table.SelectRows(Source, (S) => S[Sale Date] < C[Sale Date] and S[Make] = C[Make]))) in #"Added Custom1"

我们必须使用以下功能以便Power Query知道我们正在查看的列的上下文。有关进一步的阅读，请查看此 Power Query M Primer 。

We have to use the functions so that Power Query knows what context we're looking at the columns in. For further reading, check out this Power Query M Primer.