Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PoweredOut
Resolver I
Resolver I

Data refresh slow.

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 ?

2 ACCEPTED SOLUTIONS

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 

Microsoft Employee

View solution in original post

BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

6 REPLIES 6
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Pete

 

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

 

Joe

ppm1
Solution Sage
Solution Sage

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

 

Microsoft Employee

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"}),

 

 

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 

Microsoft Employee

Thank you Pat

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors