Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Syndicate_Admin
Administrator
Administrator

Filter and DIstinct Rows

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.

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi,

dufoq3_0-1705671787162.png

 

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

3 REPLIES 3
dufoq3
Super User
Super User

Hi,

dufoq3_0-1705671787162.png

 

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Syndicate_Admin
Administrator
Administrator

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
Super User
Super User

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors