Power Query: Filter to Latest Date Row

[HenryJS]

Hi all,

How can I filter to only show the row with the latest date for each externalid below?

So only the row with 07/05/2022 for 3929 would show as it is the latest date.

[AdHuikeshoven]

The currently accepted solution depends on modifyin M code in the advanced editor. This is not necessary. The way to do it is to group on doctype and aggregate on the Max of Expiry, add an aggregate level called expansion and select all rows. Next step is to expand and add a conditional column with name "Latest?". Compare column Expiry with column Max of Expiry, and if equal, enter "latest", otherwise "null". Filter column "Latest?" on "latest". If necessary you can delete columns "Max of Expiry" and "Latest?", and you are done.

[v-luwang-msft · Accepted solution]

Hi @HenryJS ，

Base data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjazNFDSUXLNLcjJr0wtUvDJTEzKzMksqVTwzCsuLUrMS04FShsZGBnpm+obGivF6gD1WBpZAgUDivLTUouLM/PzEnOAqlNSc/Nw6DNH1obXBuJUGkJUxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [externalid = _t, doctype = _t, Expiry = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"externalid", Int64.Type}, {"doctype", type text}, {"Expiry", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"externalid"}, {{"allrows",each Table.AddIndexColumn(
        Table.Sort(_,{{"Expiry", 
        Order.Ascending}}), "Row Rank",1,1),  each _, type table [externalid=nullable number, doctype=nullable text, Expiry=nullable date]}}),
    #"Expanded allrows" = Table.ExpandTableColumn(#"Grouped Rows", "allrows", {"externalid", "doctype", "Expiry", "Row Rank"}, {"allrows.externalid", "allrows.doctype", "allrows.Expiry", "allrows.Row Rank"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded allrows", each ([allrows.Row Rank] = 1))
in
    #"Filtered Rows"

Final output:

refer:

https://data-witches.com/2020/10/21/adding-a-row-rank-based-on-a-different-column-with-power-query/ 

Best Regards
Lucien

[Rygaard]

on your document table make a new colum.

where you write True/false  pr line if this is the latest date for this External ID.

New colum:

Max_date_by_esternal_ID =

IF( 

calculate(Max(Document[Date], filter(document, Document[ExternalID], Earlier([Document[ExternalID])))=Document[Date] ,

True(),

False()

)

the EARLIER will take the External ID from the Line it is currently on and use that as a filter to get the max date

NOW you  have a TRUE / False Column you can use as a filter

Mine look like this and work

so you would get 3 True lines

since external id 3929 has 2 Expiry dates that are both the latest date avaliable for that Id

[serpiva64]

Hi,

if you want to filter in power query:

and it's done.

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[serpiva64]

Sorry, 

i didn't understand your question.

If you are able to post some sample data (to do this you can select the colums and with right click select copy and then paste here)  i think it is possible

[HenryJS]

@serpiva64 this only returns the latest date for doctype column

it does not return latest doctype for each externalid

how can i do this in Power Query?

[Anonymous]

Try something similar

CALCULATE (
    MAX ( calendertable[Date] ),
    FILTER (
        ALL ( calendertable[Date] ),
        salestable[TotalSales]>0
    )
))