Inefficient function?

Hi Aaron,

if that's lightning-fast, let's make match just one row. Therefore you have to create "TeamLookup"-Table like this:

let

fnFillDateIntervalls = (Table as table, DateColumn as text, FillUpUntil as date)=>

let

//DebugParameters:
//Table = #"Staff Movements",
//DateColumn = "Joined",

    Source = Table,
    #"Sorted Rows" = Table.Sort(Source,{{DateColumn, Order.Descending}}),
    Index = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    Change = Table.TransformColumnTypes(Index,{{DateColumn, Int64.Type}}),
    #"Added Custom" = Table.AddColumn(Change, "Custom", each [Index]+1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Index"},#"Added Custom",{"Custom"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {DateColumn}, {"NewColumn.Date"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded NewColumn", "Custom.1", each try {Record.Field(_,DateColumn)..[NewColumn.Date]} otherwise {Record.Field(_,DateColumn)..Number.From(FillUpUntil)}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{DateColumn, "Index", "Custom", "NewColumn.Date"}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Columns", "Custom.1"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.1",{{"Custom.1", DateColumn}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{DateColumn, type date}})
in
    #"Changed Type",

    Source = #"Staff Movements",
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"All", each fnFillDateIntervalls(_, "Joined", Date.From(DateTime.LocalNow())), type table}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Joined", "Team"}, {"Joined", "Team"})
in
    #"Expanded All"

This query transforms your "Staff Movements"-table into a table with one row for each day. You should disable load to the datamodel. So although this lengthens your table considerably, the result is still very fast, as it used the simple query you mentioned in your last post.

Edit: Don't use Internet-Explorer or Edge to copy this code but preferrably Firefox. Otherwise the code will break (this time will greet you with a request for a comma where there is already one) !!!