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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
SHOOKANSON
Advocate I
Advocate I

Will multiple merges slow down my refresh?

Hi all,

 

I'm currently working on combining several datasets into one query. I am wondering if doing multiple merges in Power Query will substantially slow down my refresh time? I need all of the fields to be in one query, so I guess my real question is....Is it better to do this with merges in power query and not "enable load" for those tables? Or load them all and use the RELATED function to create custom columns? I hope this makes sense! Just looking for "best practices"!

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @SHOOKANSON ,

 

Power Query merges are very resource-expensive so, yes, multiple merges will definitely slow your refresh down considerably, although this work will be borne by your gateway(s) so not necessarily the end of the world, depending on how frequently you plan to refresh.

 

Your proposed alternative of using RELATED to create calculated columns is similarly resource-expensive, the difference though being that you will be offloading the work to the enduser, therefore potentially causing poor report performance and/or resource failures. The use of calculated columns in general is strongly advised against for this reason.

 

In terms of best practice, you should be looking to pass all of the individual tables to the data model, then use relationships to create 'virtual merges' between them, and measures to calculate across them.

You want to be aiming for a STAR or SNOWFLAKE schema model as this will best leverage Power BI's working structure.

 

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

1 REPLY 1
BA_Pete
Super User
Super User

Hi @SHOOKANSON ,

 

Power Query merges are very resource-expensive so, yes, multiple merges will definitely slow your refresh down considerably, although this work will be borne by your gateway(s) so not necessarily the end of the world, depending on how frequently you plan to refresh.

 

Your proposed alternative of using RELATED to create calculated columns is similarly resource-expensive, the difference though being that you will be offloading the work to the enduser, therefore potentially causing poor report performance and/or resource failures. The use of calculated columns in general is strongly advised against for this reason.

 

In terms of best practice, you should be looking to pass all of the individual tables to the data model, then use relationships to create 'virtual merges' between them, and measures to calculate across them.

You want to be aiming for a STAR or SNOWFLAKE schema model as this will best leverage Power BI's working structure.

 

Pete



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

Proud to be a Datanaut!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors