Filter and DIstinct Rows

[Syndicate_Admin]

I have a query that needs to be filtered by farthest date and then distinct per email and I am really having trouble whether If conditions are possible in filtering rows? Thank you in advance for any help.

[dufoq3 · Accepted solution]

Hi,

Change 2nd step YourSource = Source (refer your data)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc47DoAwDAPQu2RGDQktlI17VB0QID6iZOb2EMFIGbw92Q4BNlkOM8rUzalfdzNIggJIg4RccgWxyKjSkFPIyE6pzVMyKmsk99F59ovIC/lO8yzbvNJ//l3l/7IW2StzEOMF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, Units = _t, Date = _t]),
    YourSource = Source,
    ChangedTypeLocale = Table.TransformColumnTypes(YourSource,{{"Units", type number}, {"Date", type date}}, "en-US"),
    GroupedRows = Table.Group(ChangedTypeLocale, {"Email"}, {{"Units", each List.Max([Units]), type nullable number},
        {"Date", each List.Max(Table.SelectRows(_, (r)=> r[Units] = List.Max([Units]))[Date]), type date}})
in
    GroupedRows

[Syndicate_Admin]

hello @Syndicate_Admin 

So the filtering I need to achieve is a bit tricky since there are multiple criteria, firstly the expected result should be distinct emails which beforehand is selected like a lookup where the record with the highest Units is chosen AND if there are multiple emails with the same highest ratio, select the one with the highest Date. 

Expected Result:

[serpiva64]

Please post some sample data and the result you want to achieve