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

Use merge both the queries, then befor expaning the column, add another custom column using the formula Table.Last() and use the merged column as impute of this function

Hi KyawMyoTun,

I have tried to solve the same problem in my way hope you found it helpful.

let
    Source = Excel.CurrentWorkbook(){[Name="Note_table"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Action ID", Int64.Type}, {"Note", type text}, {"Type", type text}, {"Date", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type" , each ([Type] = "Tender")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Descending}}),
    Statusvalue=Table.FirstN(#"Sorted Rows",1),
    values1=Statusvalue{0}[Note],
    Jointable=Table.NestedJoin(#"Sorted Rows", {"Action ID","Date"}, Transaction_table, {"ID","Date"}, "Transaction_table", JoinKind.Inner),
    #"Expanded Transaction_table" = Table.ExpandTableColumn(Jointable, "Transaction_table", {"Status"}, {"Status"}),
    Custom1 = Table.FromRecords(
    Table.TransformRows(
        #"Expanded Transaction_table", 
        each _ &[Note = Text.Replace([Note], [Note], values1)]
    )
)[[Action ID],[Status],[Date],[Note]]
in
    Custom1

Hi 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/

Hi KyawMyoTun,

Thank you for reaching out to Microsoft Fabric Community.

The issue you are experiencing is caused by multiple rows being returned for each ID because the Note table contains multiple Notes for the same Action ID. We need to ensure only the latest note for the "Tender" type is merged. Here is the step-by-step procedure to resolve this:

• Go to the Note table in Power Query, click the dropdown in the Type column and select only Tender.

• Click the dropdown in the Date column and select Sort Descending and click OK

Now Go to Home → Group By, In the Group By dialog:

• Group By: Select Action ID
• Operation: All Rows
• New Column Name: GroupedNotes

• We have a summary table with each Action ID and its latest Note. Now click the small table icon next to the GroupedNotes column to expand it and select only the Note column

• Keep only the first row for each group, from Home tab click on Keep Rows and select Keep Top Rows. Enter 1 and click on OK

• Now go to the Transaction table in Power Query and from the Home tab click on Merge Queries

• Select the Transaction table as the primary table and filtered Note table as the secondary table.
• Match the ID column from the Transaction table with the Action ID column from the Note table and from Join Kind use the Left Outer Join

• After merging, a new column will appear in the Transaction table like below. Click the expand icon next to the new column and select only the Note column.

• After following these steps, the resulting table will look like this

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Thank and regards,

Anjan Kumar Chippa

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