Forum Discussion
Replicate countif excel function in PowerQuery
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 |
=let a=Table.Buffer(Table.Group(PreviousStepName,"Item",{"n",Table.RowCount})) in Table.AddColumn(PreviousStepName,"Count of Items",each a{[Item=[Item]]}[n])
4 Replies
=let a=Table.Buffer(Table.Group(PreviousStepName,"Item",{"n",Table.RowCount})) in Table.AddColumn(PreviousStepName,"Count of Items",each a{[Item=[Item]]}[n])
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...
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.
