Latest Comment per Task - wrong value upon Merge

[Anonymous]

Dear Community,

I have 2 tables, Tasks (one record for each task_id) and Time Item( multiple records for same task_id - that includes different comments added on different dates date_worked)

I need to bring the latest comment added against the task to the Task table. Unfortunately I can't use DAX, as I need all transformations done in Power Query, because I have a flow in Power Automte set up, that is programmed to export data into a CSV file.

In the Time Item table I have applied the following steps, that allowed me to filter the latest comment for each task, however when I merge this table with Task table, I see that other comments are asigned and not the latest.

#"Sorted Rows" = Table.Sort(#"Expanded wh_time_subitem",{{"task_id", Order.Ascending}, {"date_worked", Order.Descending}}),
#"GroupedTable"=Table.Group(#"Sorted Rows",{"task_id"},{{"LastItem",each Table.FirstN(_,1)}}),
#"Expanded LastItem" = Table.ExpandTableColumn(GroupedTable, "LastItem",

I really don't know what I am doing wrong, and it is frustrating to see that I have managed to select latest comment, but through the Merge step I am loosing the latest one, and a random one gets picked up.

Can you please help? Maybe there is anything else I can try? I have more experience in DAX so I am a bit lost.

Would really appreciate your help!

[PwerQueryKees · Accepted solution]

Please allow us to help you and share

• a sample of your data,
• the result you are looking,
• the result you are getting,
• any relevant intermediate results and
• ALL M code leading to those result (you mention a merge step giving the wron result, but I don't see it).

[Anonymous]

Thanks for your reply. I have managed to complete this with the Group.max function. Thanks for your help