CODEWITHSUNDEEP
powerbidaxpowerquery
Ronit Sharma
Ronit Sharma

Reputation: 41

Is there any way to check if row is unique in PowerBI?

I am working on a PowerBI dashboard where I had to compare two rows to check if it is unique or not. For example - I have data like this

Book Number  
123456             
123456       
123456       
98765        
98765        
107563       
107563

I want result like this -

Book Number  Unique or not
123456       1      
123456       0
123456       0
98765        1
98765        0
107563       1
107563       0

It is easily achievable in excel using IF(A1=A2, 0, 1) but I am having hard time achieving it in PowrBI.

What are the possible ways to do it?

Upvotes: 0

Views: 1716

Answers (1)

Marc Pincince
Marc Pincince

Reputation: 5202

(I updated this answer to include a sort before checking for duplicates. If you want the outcome to look exactly like what you show in your example above, then don't include the sort that I included, since it re-sorts the Book Numbers. But, you will need to sort them somehow before you check for duplicates.)

Here's a Power Query solution for what you show, rather than what you describe:

It starts with a table named Table, that looks like this:

enter image description here

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Sorted Rows" = Table.Sort(Source,{{"Book Number", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 0 then 1 else if [#"Book Number"] = #"Added Index"[#"Book Number"]{[Index]-1} then 0 else 1)
in
    #"Added Custom"

It yields this:

enter image description here

Upvotes: 2

Related Questions