Forum Discussion

RyanL-EIS's avatar
RyanL-EIS
Frequent Visitor
9 years ago

Latest Record in Group via Query Editor

We are trying to get the latest record/row in a group via Query Editor.  After we expand the group and we filter on the latest record, it filters the entire dataset and not within the group.  Please ...
Need Help
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

The way to go is to adjust the generated code for #"Grouped Rows", more specifically the part each _

Here you can specify the selection.

 

This should work, but I can't test of course:

 

#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"settleID", "productionDate"}, {{"SettleID-Statement_Group", each Table.SelectRows(_, let latest = List.Max(_[#"SettleID-Statement_Group.statementDate"]) in each [#"SettleID-Statement_Group.statementDate"] = latest), type table}}),

 

 

vgeldbr's avatar
vgeldbr
Icon for Helper IV rankHelper IV
4 years ago

This formula solved my problem for me (thanks!). I'm using it in a dataflow. However, it performs extremely slowly. Is that expected?

 

I'm doing a Table.Group on a linked table (based on some ingested and transformed Excel files) that is 1,996,452 rows and 19 columns. Unfortuntely the columns are all text type except for a date column and I'm using the technique described her to get the latest dated row from the grouping. I then need to expand the table to get the columns from that row.  MarcelBeug