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

[KyawMyoTun]

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

Note table

Action ID	Note	Type	Date
111	Tender Preparing	Tender	1/20/2024
111	Sent Proposal for Tender	Tender	3/20/2024
111	Lead initiated	Deal	5/20/2024
111	Negotiation process for tender	Tender	5/20/2024

Iwould like to merge and get the data from note table as below

ID	Status	Date	Note
111	Lead	1/20/2024	Negotiation process for tender
111	Proposal	3/20/2024	Negotiation process for tender
111	Completed	5/20/2024	Negotiation process for tender

In this process of merging, I just only want to get the note of "Tender" type.
I am not able to filter the note table before merge because that uses by other tables(deal type).
Please take a look and help to get this in power query? Thanks.

Regards,
KMT

[dufoq3]

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

[Krishana_123]

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

[Thejeswar · Accepted solution]

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,

[KyawMyoTun]

Dear @Thejeswar ,

  This is working perfectly and thanks for your help.

Regards,
KMT

[v-achippa]

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

[KyawMyoTun]

Dear @v-achippa ,
  Thanks.
In this scenario, I am not able to touch the note table because 
that note table is used by other tables.
If I filter as only "Tender", the deal transaction couldn't get the Type"Deal".

[pcoley]

@KyawMyoTun you don´t need to modify the note table. 
please right click over the note table query and make a reference table. powerquery will create a new table based on the note table: each time the original note table is updated then the referenced table will be also updated; the transformations made on this new table won´t affect the original note table. 

the rest of the solution can be taken from previous responses.

[KyawMyoTun]

Dear @pcoley ,
 Thanks and I get your point by creating reference table
and that will solve current issue.
I would like to request you to provide additional suggest without creating reference table.
Thanks.

Regards,
KMT

[pcoley]

Powerquery gives the oportunity to reference a table if you don´t want to modify or add steps to a query. Another thing that you can do is convert the steps into records, make all the transformations needed and call the respective step/record.  Please check Chandeep (goodly) explanation of this option at https://www.youtube.com/watch?v=weafpG2yG1w

[Omid_Motamedise]

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

[KyawMyoTun]

Dear @Omid_Motamedise ,

  How can I extract only "Tender" type without touching note table.
Can you please guide me more details? Thanks.

[tackytechtom]

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/

[KyawMyoTun]

Dear @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