Merge data based on ID with latest date of multiples value and by filter

Hi KyawMyoTun ,

I am building on top of the solutions posted here.

As you don't want to create a Reference table separately, we can have a virtual reference table built in the Advanced Properties of the Transactions table and use that to merge with the note table. The Below is the M-Query. This Query will work with the Sample Data that you shared.

let
    reference = Note,
    #"Filtered Rows" = Table.SelectRows(reference, each ([Type] = "Tender")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Action ID"}, {{"Count", each _, type table [Action ID=nullable number, Note=nullable text, Type=nullable text, Date=nullable text]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Note"}, {"Count.Note"}),
    #"Kept First Rows" = Table.FirstN(#"Expanded Count",1),
    Source = <<Give your transaction table here >>,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status", type text}, {"Date", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Kept First Rows", {"Action ID"}, "firstrow", JoinKind.LeftOuter),
    #"Expanded firstrow" = Table.ExpandTableColumn(#"Merged Queries", "firstrow", {"Count.Note"}, {"firstrow.Count.Note"})
in
    #"Expanded firstrow"

Basically I am creating a virtual reference table and using that to merge with the transaction table to get the output. In the above M Query replace the Source with your table details.

M Query Snapshot for your reference:

My FInal table will look like this

Regards,