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,
Community SupportHi 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
KyawMyoTunDear 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
Super User
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
