PowerQuery COUNTIF以前的日期 [英] PowerQuery COUNTIF Previous Dates

问题描述

我对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.

这篇关于PowerQuery COUNTIF以前的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！