Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional Row count

Hello guys, I'm newbie to PowerBI and I need some logical help.   I want to count each unique row value as '1' or if duplicate '0". Would appreciate your help.    Thanks, James
Need Help
  • PurpleGate's avatar
    PurpleGate
    4 years ago

    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:

        

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How do we count duplicates?

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
4 years ago

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