Need help Optimizing a function: Return values from another row by previous date

[ercoc]

Hi, first post and I know it could be more complete. I will do my best to provide more clarity as I find time. Thank you for your patience

Background:

I started working with a non-profit which has placed some unexpected restrictions on my work. Specifically, they require all ETL steps to take place in Power BI, steps that I'd normally outsource to SQL, etc. In general, I've been having fun trying to decide how best to balance computational costs between Power Query and DAX. E.g., I wouldn't have expected GroupBys to run so much faster in DAX. However, I'm having trouble optimizing an approach for a critical metric. The function and the following record expansion works as is but the refresh is very slow and often times-out making it too inefficient.

Question:
How can optimize this function for runtime?

Function: GetMaxPreviousMeeting

GetMaxPreviousMeeting = (currentRow, rows) => // function to efficiently capture  
               //  the previous meeting date/type for each speaker’s meeting.
  let
    filteredRows = Table.SelectRows(rows, each [speaker] = 
      currentRow[speaker] and [MeetingDate] < currentRow[MeetingDate]),
    maxDate = List.Max(filteredRows[MeetingDate]),
    meetingType = if maxDate <> null then 
      Table.First(Table.SelectRows(filteredRows, each [MeetingDate] = maxDate)) 
      [Type] else null
  in
    [PreviousMeetingDate = maxDate, PreviousMeetingType = meetingType]

=Table.AddColumn(Source, "Previous Meeting Info", 
    each #"Function:GetMaxPreviousMeeting"(_, Source))

= Table.ExpandRecordColumn(#"AddPreviousMeetingInfo", "Previous Meeting Info", 
    {"PreviousMeetingDate", "PreviousMeetingType"}, {"PreviousMeetingDate", 
    "PreviousMeetingType"})

Data:

The data consists of actions as rows, where one or more actions/rows can associated with the same meeting. A Unique meeting is identified by a unique combination of speaker, meeting date, and type. We want previous type and date to calculate a conditional deadline for the current row to find meeting timeliness.

Goal:

Speaker	Type	MeetingDate	Action	PreviousMeetingDate	PreviousMeetingType
John	A	09/20/2023	1	09/03/2023	C
John	A	09/20/2023	4	09/03/2023	C
Bob	B	09/13/2023	3	null	null
John	A	09/20/2023	2	09/03/2023	C
John	C	09/03/2023	3	null	null

Thank you!

[ercoc · Accepted solution]

In the end, I decided to move the function to DAX instead and it works as intended without putting too much strain on end-user xp.