if语句 power query,PowerQuery COUNTIF以前的日期

I'm a little rusty on PowerQuery.

I need to count "previous" entries in the same table.

For example, let's say we have a table of car sales.

For the purposes of PowerQuery, this table will be named tblCarSales

I need to add two aggregate columns.

The first aggregate column is the count of previous sales.

The Excel formula would be =COUNTIF([Sale Date],"

The second aggregate column is the count of previous sales by make.

The Excel formula would be =COUNTIFS([Sale Date],"

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

... 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"

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.