Find Duplicate then filter on value

[DavidG007]

Hi All,

I have a Power Query that works fine, however, I also have duplicate lines that I have also managed to flag as 'Duplicate', BUT, my problem is this;

So that I am able to filter, I need to flag just the one Duplicate line but it needs to flag the one that has the highest value in the column "Run Order"

Sounds simple but am really struggling, anyone have any idea's? anything would be really appreciated.

Account Number	Sales Code	Run Order	Duplicate Check
12345	ABC	0	DO NOT FLAG AS DUP
12345	ABC	100	FLAG THIS LINE AS A DUP

 

[sandy666]

maybe Group then Max for Run Order

 

[JustynaMK]

Hi David,

Try this:

Go to Transform > Group By > Advanced >

A new table will be now visible in your Queries. Go to Home > Merge Queries > make sure to select both "Account Number" and "Sales Code" columns when merging:

Now expand Table1B by only selecting Run Order Max column:

One last step - add a conditional column (Add Column > Custom Column):

= Table.AddColumn(#"Expanded Table1B", "Custom", each if [Run Order]=[Table1B.Run Order Max] then [Run Order] else 0)

It will return max value or zero for each row. Feel free to change it to 1 and 0 if you want to flag each row.

Alternatively, here's DAX formula if you wish to create a new column straight in PowerBI app:

cDuplicate = 
    IF([Run Order] <>
        CALCULATE(
            MAX(Table1[Run Order]),
            ALLEXCEPT(Table1, Table1[Account Number], Table1[Sales Code])
        ),
        0,
        [Run Order]
    )

Hope it helps!

 

[DavidG007]

Hi Justyna,

Thank you very much for taking the time and effort with your reply, it was great, I followed each step and it has indeed solved my issue.

Thanks once again, really appreciated

 

[JustynaMK]

Awesome news, glad it worked for you best wishes