Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
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
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:
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
Proud to be a Super User!
How do we count duplicates?
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.