Will multiple merges slow down my refresh?

[SHOOKANSON]

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"!

[BA_Pete · Accepted solution]

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