Data refresh slow.

[PoweredOut]

Within Power Query, I have merged two tables based on a date range. I followed the instructions within this article. https://exceed.hr/blog/merging-with-date-range-using-power-query/

It's working as expected, but it takes a long time to refresh. I have also implemented Table.Buffer, but refresh times are still the same. Do any Mquery experts have some suggestions? 

@RickdeGroot ?

[ppm1 · Accepted solution]

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 

[BA_Pete · Accepted solution]

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

[PoweredOut]

Hi Pete

Thanks for your resolution, Pat's solution worked for me.

Joe

[ppm1]

Please share your M code for both queries from the Advanced Editor to get specific suggestions. Also how many rows in each table? And how long is it taking?

Pat

[PoweredOut]

Good morning Pat

I'm merging table EmployeeData which has around 1000 Rows (This will grow over time, but nothing major) with a AvailableDays table which has 100K+ (Will also grow, again nothing major)

Thanks

Joe

#"Changed column type 3" = Table.TransformColumnTypes(#"Filtered Rows", {{"AvailableHours", type number}}),
    BufferedTable = Table.Buffer(#"EmployeData"),
  
    #"Added Custom99" = Table.AddColumn(#"Changed column type 3", "Custom99", 
    (S) => Table.SelectRows(#"EmployeeData", (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"}),

[ppm1 · Accepted solution]

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 

[PoweredOut]

Thank you Pat

It was the table reference that was the issue. The table refreshed in seconds.