Transform data only most recent

[Anonymous]

Hi all, how do I use Query Editor to filter out the records with the old date? For example, with Ticket ID 12 and 14 I only want to keep the record with the most recent date (9/21/20 for Ticket 12, 9/25/20 for Ticket 14). Thanks!

Current Table

Ticket ID	Date	Status
12	9/20/20	Open
12	9/21/20	Closed
14	9/24/20	Open
14	9/25/20	Open

Desired Table

Ticket ID	Date	Status
12	9/21/20	Closed
14	9/25/20	Open

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Create a measure as below:

Day = 
var _latestday=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Ticket ID]=MAX('Table'[Ticket ID])))
Return
IF(MAX('Table'[Date])=_latestday,MAX('Table'[Date]),BLANK())

Or a calculated column as below:

Column = 
var _latestday=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Ticket ID]=EARLIER('Table'[Ticket ID])))
Return
IF('Table'[Date]=_latestday,'Table'[Date],BLANK())

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@amitchandak @Greg_Deckler any ideas on this?

[CNENFRNL]

Hi, @Anonymous , you might want to try this solution,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRS0lGy1DcyACIgy78gNU8pVgchbggRd87JL05NgciYQGRM0HVAxU2RxWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket ID" = _t, Date = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket ID", Int64.Type}, {"Date", type date}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Ticket ID"}, {{"Latest", each _{[Date = List.Max([Date])]}}}),
    #"Expanded Latest" = Table.ExpandRecordColumn(#"Grouped Rows", "Latest", {"Date", "Status"}, {"Date", "Status"})
in
    #"Expanded Latest"

[darentengmfs]

Hi @Anonymous 

Please take a look at the following article:

https://community.powerbi.com/t5/Desktop/Latest-Date-Filter/td-p/556579

You might have to change some of the parameters to suit your dataset.