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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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