CODEWITHSUNDEEP
excelcountpowerpivotpowerquery
Kelli D
Kelli D

Reputation: 13

Power Query or DAX - Count number of times a value is present in a column

I have a table and column A has IDs. I would like to count how many times each ID is present in the column so it would hopefully look something like this:

Column A IDs Column B Count 1 1 12 4 10 1 12 4 12 4 36 2 36 2 12 4

I know how to do this in Excel but I am trying to do this in Power Query and/or Power Pivot.

Please help!

Upvotes: 1

Views: 6297

Answers (2)

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4144

In Power Query you can use Group By. The default UI action for Group By on the ID column will do this. The M formula will look like this:

Table.Group(#"Changed Type", {"IDs"}, {{"Count", each Table.RowCount(_), type number}})

Upvotes: 2

Adam K
Adam K

Reputation: 56

Make a calculated column in powerpivot with this formula:

=CALCULATE(counta(Table[ID]),filter(Table[ID]=EARLIER(Table[ID])))

Upvotes: 1

Related Questions