Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Transform data only most recent

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 IDDateStatus
129/20/20Open
129/21/20Closed
149/24/20Open
149/25/20Open

Desired Table

Ticket IDDateStatus
129/21/20Closed
149/25/20Open
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

4 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    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"
  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    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!