Forum Discussion

KyawMyoTun's avatar
KyawMyoTun
Icon for Helper IV rankHelper IV
1 year ago
Solved

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

Dear Experts, I have two table called transaction and note table as below. Transaction table ID Status Date 111 Lead 1/20/2024 111 Proposal 3/20/2024 111 Completed 5/20/2024 ...
Need Help
  • Thejeswar's avatar
    Thejeswar
    1 year ago

    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,

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
1 year ago

Hi KyawMyoTun, check also this:

 

Output

let
    T_Tbl = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJR8klNTAFShvpGBkBkZKIUqwOTCijKL8gvTswBMo2xSDvn5xbkpJakgrSbIuRjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, Date = _t]),
    N_Tbl = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRCknNS0ktUggoSi1ILMrMS4cLARmG+kYGQGRkohSrA1MfnJpXAlSdX5BfnJijkJZfpABXDmcYY9Hnk5qYopCZl1mSmViSmgIUcElNzAFSpljU+qWm54PUZebnKRQU5SenFheDbSrBsAlJdywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Action ID" = _t, Note = _t, Type = _t, Date = _t]),
    T_TblChangedType = Table.TransformColumnTypes(T_Tbl,{{"ID", Int64.Type}, {"Date", type date}}, "en-US"),
    N_TblChangedType = Table.TransformColumnTypes(N_Tbl,{{"Action ID", Int64.Type}, {"Date", type date}}, "en-US"),
    LatestTenderNotes = Table.Combine(Table.Group(N_TblChangedType, {"Action ID"}, {{"T", each Table.SelectRows(_, (x)=> x[Type] = "Tender" and x[Date] = List.Max([Date])), type table}})[T])[[Action ID], [Note]],
    Merged = Table.RemoveColumns(Table.Join(T_TblChangedType, "ID", LatestTenderNotes, "Action ID", JoinKind.LeftOuter), {"Action ID"}, MissingField.Ignore)
in
    Merged