Replicate countif excel function in PowerQuery

[Anonymous]

Hi,

I'm trying to replicate countif excel function into powerquery.

=COUNTIF(C:C,C3)

End result should be as below

Item	Count of Items
A	4
A	4
A	4
A	4
B	4
B	4
B	4
B	4
B	4

[wdx223_Daniel · Accepted solution]

=let a=Table.Buffer(Table.Group(PreviousStepName,"Item",{"n",Table.RowCount})) in Table.AddColumn(PreviousStepName,"Count of Items",each a{[Item=[Item]]}[n])

[HotChilli]

Is that a mistake on the B rows?

If I understand correctly, you can do it like this:

Do a 'Group By' with only 'All Rows'

Then add a custom Column using Table.RowCount on the column that the previous step created.

Expand the rows. That's it.

[jgeddes]

Table.AddColumn(#"previousStep", "Count of Items", each List.Count(Table.SelectRows(#"previousStep", (x) => x[Item] = "A")[Item]))

gets you...

Table.AddColumn(#"Changed Type", "Count of Items", each List.Count(Table.SelectRows(#"Changed Type", (x) => [Item] = x[Item])[Item]))

gets you...

[Anonymous]

Unfortunately this does not work. Below is the formula Im using

= Table.AddColumn(#"Added Unit", "Count of Items", each List.Count(Table.SelectRows(#"Added Unit", (x) => [a.itm_nbr]=[a.itm)[a.itm]))