Conditional Row count

[Anonymous]

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

[KT_Bsmart2gethe]

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

[PurpleGate · Accepted solution]

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:

    

[Nathaniel_C]

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

[Anonymous]

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