Filter rows based on unique preferance values

[awolf88]

Hi guys, 

Currently struggling with a little problem here and was hoping to get from input from you geniusses on how to solve my problem the easiest way:

The data I receive from our ERP is pretty messy: It shows a few entries after changes of "Sales Personell" has been made within the system and there seems to be no way to clear it up within the ERP itself. So I'm trying to sort the following in PowerQuery instead:

In my example, there are 2x2 rows that show duplicate Order entries (Order Number 16526 and 16823). The issue with the ERP is that it shows up twice because the Sales Person behind it has been adjusted. Instead of deleting/editing the rows, it creates a new one. 

What I'm trying to achieve is 2 things:

1. I would want to filter out unique orders only and remove the "duplicate" row (that is unique with exception of "Sales Person"

2. I would need it to preferably choose Sales Person "Martin" or "Alex" over anyone else who was assigned with the order. 

So in our example i would need row #4 (Order number 16526 with Sales Person "Employee 1") and row #7 (Order number 16823 with Sales Person "Employee 3") removed / filtered out. 

Appreciate your input as always, my friends!

Best regards,

Alex

[awolf88]

Hi ValtteriN!

Thanks for the rapid response! Did the trick for me! 

Many thanks!

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:

1. Add conditional column: = Table.AddColumn(Source, "S_test", each if List.Contains({"Alex","Martin"},[Sales person]) then 1 else 0)

This will give 1 to rows with either Alex or Martin

2. Sort by this column in descending order
3. use table.distinct on all but Sales person and the new conditional column:
= Table.Distinct(#"Changed Type", {"Order","Customer", "Sales date", "QTY"})

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/