How to keep rows with the most recent date

[AW1976NOVA]

Hello,

I have an excel table I'm bringing into Power BI.  I'm trying to figure out how to keep only the rows with the most recent date (Oil Change Date).

Here is an example of the table:

Here is an example of whayt my results should look like once properly manipulated:

Is this something a combination of transformation operations in Edit Query can handle?  I'm still learning Power BI.

Thank you in advance!

[AdHuikeshoven]

This can be done without editing M code. Group on "Member ID" and select Max of "Oil Change Date" call it "Max Date", and add aggregate level "expansion" including all rows. Expand, and add conditional column "Latest?". Compare "Oil Change Date" with "Max Date" if equal enter "latest", otherwise "null". Filter on "latest" in column "Latest?". If necessary delete columns "Max Date" and "Latest?"

[v-juanli-msft · Accepted solution]

Hi @AW1976NOVA 

In edit queries, 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssvSlHSUTLVt9Q3MjA0UIrViVZy8g0HClnqG4KEjMFCIanFOYlAQUMDfSOwsDlY2DkjtawSrN0UKGhgARaEGmmuD1JsYGiIotLQECJsYIlirjFUMcQEr9TUApBafWOQmCWyqUD9xjBjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, #"Oil Change Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Oil Change Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {"AllRows", each _, type table [Group=text, Oil Change Date=date]}),
    RankFunction = (tabletorank as table) as table =>
     let
      SortRows = Table.Sort(tabletorank,{{"Oil Change Date", Order.Descending}}),
      AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
     in
      AddIndex,
    AddedRank = Table.TransformColumns(#"Grouped Rows", {"AllRows", each RankFunction(_)}),
    #"Expanded AllRows" = Table.ExpandTableColumn(AddedRank, "AllRows", {"Oil Change Date", "Rank"}, {"AllRows.Oil Change Date", "AllRows.Rank"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded AllRows", each ([AllRows.Rank] = 1))
in
    #"Filtered Rows"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Few ways.

In visualization take the max of date and HP.

Second

you can create a summarize table

summarize(table,table[group],table[member id],table[color],table[max speed],"Oil change date",max(table[Oil change Date]),"HP",max(HP))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin