Count Duplicate Occurrences

Question

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&nbsp;occurrences of duplicate values. For example:&nbsp;Person&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;aaabccdddde&nbsp;With this column, I'd like to see this happen.&nbsp;Person&nbsp; &nbsp; &nbsp; Occurrencea&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4e&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;OR&nbsp;Person&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Occurrencea&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp;d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4e&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp;&nbsp;&nbsp;Does that make sense? How can I accomplish that?&nbsp;

eric_zhang · Answer

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

&nbsp;
See the attached pbix file.

anonymous · Answer

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

johnathon_s · Answer

Neither of those worked. I have&nbsp;large amounts of data in these tables, including many many columns. I want to count the occurrences&nbsp;that a name shows up in one column.&nbsp;&nbsp;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.&nbsp;&nbsp;Solution two&nbsp;Measure 2 = CALCULATE(COUNTA(yourTable[Person]),ALLEXCEPT(yourTable,yourTable[Person]))Creates massive amounts of unneeded rows.&nbsp;&nbsp;&nbsp;

eric_zhang · Answer

Johnathon_S wrote:
Neither of those worked. I have&nbsp;large amounts of data in these tables, including many many columns. I want to count the occurrences&nbsp;that a name shows up in one column.&nbsp;
&nbsp;
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.&nbsp;
&nbsp;
Solution two&nbsp;
Measure 2 = CALCULATE(COUNTA(yourTable[Person]),ALLEXCEPT(yourTable,yourTable[Person]))
Creates massive amounts of unneeded rows.&nbsp;&nbsp;
&nbsp;

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 · Answer

Here is a more specific example. Sensitive information has been redacted.&nbsp;&nbsp;&nbsp;As you can see there is quite a lot of data. Many of the fields have been minimized.&nbsp;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.&nbsp;&nbsp;&nbsp;

Forum Discussion

Count Duplicate Occurrences

6 Replies

Related content

Count occurrence

Column count duplicates

Count duplicate values

Count each occurrence

Duplicate ranking