I’ve not been able to find a good doc on what I should expect from this option.
My PowerQuery runs very slow. Every time I make a data transformation it takes 6-7 minutes to update. My understanding is that by turning off background refreshes when i make a PowerQuery change it should not go back and try and refresh the whole dataset? Is this the correct expectation? I’m actually finding very little difference in the performance when this on or off. Any suggestions on this topic or any good videos/docs to recomend?
Hi @chad.sharpe ,
Thank you for reaching out to the community.
While we wait for other members to share their insights, we’ve taken the liberty of using “Explain Simply,” one of the tools available within Data Mentor
Thank you for reaching out to the community.
While we wait for other members to share their insights, I’ve taken the liberty of using EDNA Chat within Data Mentor:
Understanding Background Refreshes in Power Query
What is Background Refresh?
- Background Refresh allows Power Query to update data in the background, enabling you to continue working without waiting for the query to complete.
Your Expectations
- Turning Off Background Refresh: You are correct in expecting that when you turn off background refresh during transformations, Power Query will not attempt to refresh the entire dataset immediately after a change. However, any time you run a query (e.g., load or refresh data), it will still process the necessary transformations which may take time.
Reasons for Slow Performance
- Complex Queries: If your queries are complex or involve large datasets, they may inherently take longer, irrespective of the refresh settings.
- Data Source Latency: The time taken also depends on the performance of the data source itself. Queries pulling data over the network can especially be slow.
- Hardware Limitations: Insufficient memory or processing power can impact performance when running multiple transformations.
Suggestions for Improvement
- Optimize Queries: Look into simplifying your queries, filtering early, and reducing the columns you’re pulling when possible.
- Incremental Refresh: If your data supports it, consider using incremental refresh strategies to process only new data.
- Monitor Resources: Use performance monitoring tools to identify if hardware bottlenecks (CPU, memory) are affecting your query performance.
- Evaluate Data Model Efficiency: Ensure your data model itself is optimized for reporting speed.
Resources
- Microsoft Documentation: Review the official Microsoft Power Query documentation for best practices and advanced settings.
- Video Tutorials:
- Look for Power Query optimization tutorials on platforms like YouTube. Channels like “Guy in a Cube” often provide practical insights and optimizations tips.
- Consider training resources on platforms like Udemy or LinkedIn Learning that focus on Power Query and data transformation best practices.
Feel free to checkout more of our Data Mentor features as you work on your report. These tools are designed to help with tasks like the one you’re working on, and they might accelerate your learning curve.
Cheers,
Enterprise DNA Support Team
1 Like