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:

        

     

     

KT_Bsmart2gethe's avatar
KT_Bsmart2gethe
Icon for Impactful Individual rankImpactful Individual
4 years ago

Hi Anonymous,

 

You use Group By to do the count then apply the conditional formula.

 

Below code is what I combine the codes for both group by and if formula:

Add a custom step:

Table.Group(TableName/PreviousStep, Table.ColumnNames(TableName/PreviousStep), {{"Count", each if Table.RowCount(_) > 1 then 0 else 1 , Int64.Type}}, GroupKind.Local)

 

Translate:

1. Get all column names from the previous step or a table with Table.ColumnNames(TableName/PreviousStep) ; hence, dynamically pick up all columns.

2. if Table.RowCount(_) > 1 then 0 else 1 , this formula does the count if each row is greater than 1 then 0 else 1

 

Regards

KT