Data refresh slow.

First thing I notice is that you are not referencing your buffered table in the function. Try this instead.

#"Changed column type 3" = Table.TransformColumnTypes(#"Filtered Rows", {{"AvailableHours", type number}}),
    BufferedTable = Table.Buffer(#"EmployeeData"),
  
    #"Added Custom99" = Table.AddColumn(#"Changed column type 3", "Custom99", 
    (S) => Table.SelectRows(BufferedTable, (P)=>   S[Date] >= P[ValidFrom] and S[Date] <= P[ValidTo] and P[CompanyID] = S[CompanyID])),
    #"Expanded Custom99" = Table.ExpandTableColumn(#"Added Custom99", "Custom99", {"EmployeeID", "CostCenterID", "FTE"}, {"EmployeeID", "CostCenterID", "FTE"}),

Pat 

Hi PoweredOut ,

I normally recommend strongly against using DAX calculated columns, but this is the one exception I've ever made to date. I actually recommend strongly in favour of doing conditional merges in DAX instead of M.

Try your query with the correctly-referenced buffered table(s) as ppm1 suggests and, if the performance is still poor, then try a DAX calculated column in your EmployeeData table like this:

Custom99 = 
CALCULATE(
    VAR __dateRow = VALUES(EmployeeData[Date])
    VAR __companyRow = VALUES(EmployeeData[CompanyID])
    RETURN
    MAXX(
        FILTER(
            OtherTable,
            OtherTable[CompanyID] = __companyRow
                && OtherTable[ValidFrom] <= __dateRow
                && OtherTable[ValidTo] >= __dateRow
        ),
        OtherTable[EmployeeID]
    )
)

You'll have to do it again for each [CostCenterID] and [FTE] but, even doing this three times, I'll bet it's still orders of magnitude faster than doing it in Power Query/M.

Pete