Forum Discussion
Will multiple merges slow down my refresh?
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
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