Count Duplicate Occurrences

[Johnathon_S]

I have been through tons of this forum searching for the answer to this. It seems like a common question, but I am unable to generate the field that I'd like. In the PowerBI query editor, or in the Report View, I'd like to count the occurrences of duplicate values. For example:

Person         

a

a

a

b

c

c

d

d

d

d

e

With this column, I'd like to see this happen.

Person      Occurrence

a               3

b               1

c                2

d               4

e               1

OR

Person         Occurrence

a                  3

a                  3

a                  3

b                  1

c                   2

c                   2

d                  4 

d                  4

d                  4

d                  4

e                  1

Does that make sense? How can I accomplish that? 

[Anonymous]

For anyone coming to this post looking for the solution, it looks like this link has the right idea - https://www.excelguru.ca/blog/2015/12/09/identify-duplicates-using-power-query/

[Eric_Zhang]

@Johnathon_S

For the first output, you just need a measure as below.

Measure = COUNTROWS(yourTable)

As to the second, you need a index column and a measure as below

Measure 2 = CALCULATE(COUNTA(yourTable[Person]),ALLEXCEPT(yourTable,yourTable[Person]))

See the attached pbix file.

[Johnathon_S]

Neither of those worked. I have large amounts of data in these tables, including many many columns. I want to count the occurrences that a name shows up in one column. 

Measure = COUNTROWS(myTable) does not have the behavior in your screenshot. Rather, the measure makes a new column with a value of "1" in every row. 

Solution two 

Measure 2 = CALCULATE(COUNTA(yourTable[Person]),ALLEXCEPT(yourTable,yourTable[Person]))

Creates massive amounts of unneeded rows.  

[Eric_Zhang]

---

@Johnathon_S wrote:

Neither of those worked. I have large amounts of data in these tables, including many many columns. I want to count the occurrences that a name shows up in one column. 

Measure = COUNTROWS(myTable) does not have the behavior in your screenshot. Rather, the measure makes a new column with a value of "1" in every row. 

Solution two 

Measure 2 = CALCULATE(COUNTA(yourTable[Person]),ALLEXCEPT(yourTable,yourTable[Person]))

Creates massive amounts of unneeded rows.  

---

@Johnathon_S

Both shall work for the given sample in your case. While it won't apply to your real case, please post more specific sample.

[Johnathon_S]

Here is a more specific example. Sensitive information has been redacted. 

As you can see there is quite a lot of data. Many of the fields have been minimized.

A solution I have done was duplicating the query table and using "Group By" in the query editor. However, I want to have that information in just one table if at all possible. 

[Eric_Zhang]

---

@Johnathon_S wrote:

Here is a more specific example. Sensitive information has been redacted. 

As you can see there is quite a lot of data. Many of the fields have been minimized.

A solution I have done was duplicating the query table and using "Group By" in the query editor. However, I want to have that information in just one table if at all possible. 

---

@Johnathon_S

What are those "Group By" columns? You can just put them along with Measure = COUNTROWS(yourTable) to a table visual.