Inefficient function?

Also: I've never used Table.IsEmpty - maybe Table.Countrows is faster - but just a vague guess.

Hi ImkeF

Thank you for your input.  It's been very helpful.  I reviewed the link and I had a bash and the resulting query is a little faster.  I wonder if it's not too much to ask for your opinion of how I complete the code?  I've found that grouping the data by name does, as you say, chunk the table up nicely.  It allows me to operate on a table per Name, which is great!

My problem is that I've not found a way to then work through each row of the table, comparing the Date with the Joined date of the Staff Movements table.  The only way I can do that is to still make a call to the original function that now doesn't require the Text.Contains([Name], lookup_name) and is therefore a bit faster.

As the result remains quite slow, am I missing a more elegant way to check the Timesheets.Date against the Staff Movements.Joined field?

My Timesheets query:

let
    Source = Sql.Databases("XXXX"),
    SystemDB = Source{[Name="SystemDB"]}[Data],
    dbo_Timesheets = SystemDB{[Schema="dbo",Item="Timesheets"]}[Data],
    //UNIMPORTANT CLEAN UP STEPS HERE
    #"Changed Type" = Table.TransformColumnTypes(dbo_Timesheets,{{"Date", type date}}),
    //Group by Name
    GroupedByName = Table.Group(#"Changed Type", {"Name"}, {{"AllRows", each _, type table}}),
    FindTeamFunction = (tabletopopulate as table) as table =>
        let
	    AssignTeam =  Table.AddColumn(tabletopopulate, "Team", each if [Name] <> "" then fnLookupTeam([Date],"Team",#"Staff movements lookup") else "NO TEAM", type text)
        in
    AssignTeam,
    //Apply the function to the AllRows column
    AddedTeam = Table.TransformColumns(GroupedByName, {"AllRows", each FindTeamFunction(_)}),
    #"Expanded AllRows" = Table.ExpandTableColumn(AddedTeam, "AllRows", {"Name", "Project", "Date", "Team"})
in
    #"Expanded AllRows"

I've modified the fnLookupTeam function to omit the Name search:

(lookup_date as date, return_column as text, lookup_table as table) as any =>
let
        /*FilterTable = Table.SelectRows(lookup_table, each Text.Contains([Name], lookup_name) and [Joined] <= lookup_date),*/
        FilterTable = Table.SelectRows(lookup_table, each [Joined] <= lookup_date),
        ReturnResult = 
            if Table.IsEmpty(FilterTable)=true
            /*if Table.RowCount(FilterTable)=0*/
            then
            "NO TEAM"
            else
            Record.Field(Table.First(Table.Sort(FilterTable,{"Joined",Order.Descending})), return_column)
in ReturnResult

If I've reached the limit of what can be done, then it's cool as I'll still have the DAX workaround; it's just a bummer as I'd much prefer to have Timesheets.Team in place in the query rather than in the table later.

Thanks for your help!

Aaron