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