cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johnathon_S
Regular Visitor

Count Duplicate Occurrences

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? 

6 REPLIES 6
Anonymous
Not applicable

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
Microsoft
Microsoft

@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]))

Capture.PNG

 

See the attached pbix file.

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

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

 

 

QuerySnip.PNG

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 wrote:

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

 

 

QuerySnip.PNG

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.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors