Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to keep rows with the most recent date

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!

 

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    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.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

    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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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

  • AdHuikeshoven's avatar
    AdHuikeshoven
    New Member
    2 years ago

    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?"