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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors