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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors