Forum Discussion
Helper IVMerge 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,
Most Valuable ProfessionalHi KyawMyoTun ,
How about this?
Here the code in Power Query M that you can paste into the advanced editor. If you do not know, how to exactly do this, please check out this quick walkthrough:
let
Source = NoteTable,
#"Filtered Rows" = Table.SelectRows(Source, each ([Type] = "Tender")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Action ID"}, {{"MaxDate", each List.Max([Date]), type nullable date}}),
#"Merged Queries 1" = Table.NestedJoin(#"Grouped Rows", {"Action ID", "MaxDate"}, #"Filtered Rows", {"Action ID", "Date"}, "Grouped Rows", JoinKind.Inner),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries 1", "Grouped Rows", {"Note"}, {"
Note"}),
#"Merged Queries 2" = Table.NestedJoin(#"Expanded Grouped Rows", {"Action ID"}, TransactionTable, {"ID"}, "TransactionTable.1", JoinKind.RightOuter),
#"Expanded TransactionTable" = Table.ExpandTableColumn(#"Merged Queries 2", "TransactionTable.1", {"ID", "Status", "Date"}, {"ID", "Status", "Date"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded TransactionTable",{"#(cr)#(lf)Note", "ID", "Status", "Date"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"ID", "Status", "Date", "#(cr)#(lf)Note"})
in
#"Reordered Columns"
In a nutshell, we reference the NoteTable in a new Query, filter it by Tender and calculate the last row per Action ID. Then we merge this with a right outer join onto TransactionTable. Lastly, it's just removing columns and reordering 🙂
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
KyawMyoTunDear tackytechtom ,
Thanks for your comment.
I am not able to change the note table here.
Is there any other way to fix without creating extra table(reference or duplicate).
Regards,
KMT