Forum Discussion
Conditional Row count
Hi, hope this helps
In Transform Data:
Add index starting from 0
Group rows by ID, or whatever column you want to be the unique identifier
Expand the table, put the index back in order of ascending then you can delete that column
I then added a conditional column called "duplicated" which I would use in a measure
Then I can see how many times each of rows [Product with the same ID] appear in my table.
The measure was
Measure = calculate(COUNTROWS('Table'),FILTER('Table','Table'[Number_of_Items]>1))
This was my original table:
Community ChampionHi Anonymous
Here is a great article by that amazing author, KenPuls . After you have a count of the duplicates, you can use the conditional column to define no duplicates as 1 or duplicates as 0.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
How do we count duplicates?
- Nathaniel_C
Hi Anonymous ,
Sorry, this did not display. https://www.excelguru.ca/blog/2015/12/09/identify-duplicates-using-power-query/ Go here, a pretty quick read, but essentially you use a groupby and then replace all the columns in a second step. You end up with one column that has the count of duplicates. I use this all the time!
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel