cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johnathon_S Frequent Visitor
Frequent 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
Moderator Eric_Zhang
Moderator

Re: Count Duplicate Occurrences

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

Johnathon_S Frequent Visitor
Frequent Visitor

Re: Count Duplicate Occurrences

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.  

 

Moderator Eric_Zhang
Moderator

Re: Count Duplicate Occurrences


@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 Frequent Visitor
Frequent Visitor

Re: Count Duplicate Occurrences

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. 

 

 

Moderator Eric_Zhang
Moderator

Re: Count Duplicate Occurrences


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

SBuri Member
Member

Re: Count Duplicate Occurrences

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/

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)