Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RyanL-EIS
Frequent Visitor

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 see below.

 

Capture1.PNG

 

let
Data = Source{[Name="Data"]}[Data],
dbo_PercentOfProceeds = Data{[Schema="dbo",Item="PercentOfProceeds"]}[Data],
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"settleID", "productionDate"}, {{"SettleID-Statement_Group", each _, type table}}),
#"Expanded SettleID-Statement_Group" = Table.ExpandTableColumn(#"Grouped Rows", "SettleID-Statement_Group", {"fileAndPage", "statementDate"}, {"SettleID-Statement_Group.fileAndPage", "SettleID-Statement_Group.statementDate"}),

#"Latest Records" = Table.SelectRows(#"Expanded SettleID-Statement_Group", let latest = List.Max(#"Expanded SettleID-Statement_Group"[#"SettleID-Statement_Group.statementDate"]) in each [#"SettleID-Statement_Group.statementDate"] = latest)

 

in

#"Latest Records"

 

Any suggestions?

 

Thanks for your help!

5 REPLIES 5
MarcelBeug
Community Champion
Community Champion

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}}),

 

 

Specializing in Power Query Formula Language (M)

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 

Hi Marcel,

 

Thanks for your help and we believe we are close.  The logic behind your code makes sense for what we are trying to accomplish; however, we are getting the following error trying to preview/expand the subtable of the grouped rows.

 

Expression.Error: The field 'SettleID-Statement_Group.statementDate' of the record wasn't found.

Any idea?

Also, should we be trying to select/filter on grouped rows at the query level (Query Editor) OR it is better to create a calculated table instead?  If so, any suggestions on the DAX for the calculated table?

Ah, I copied the code from your last line of code, but fields were renamed, so you shouldn't use 'SettleID-Statement_Group.statementDate', but just statementDate in the code I supplied.

 

The question about DAX or Power Query: my specialty is Power Query and I know little about DAX, so I'm pretty biased, but I think it's safe to state that you should use:

Power Query if you don't need to have the detailed data (before grouping) in your data model, while
DAX might be the better option if you need both the detailed and grouped information, as this will take into account any filter context.

 

As I know little about DAX, others will be better able to help you with any DAX solution.

 

 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.