Optimizing Power Query for Performance: A Case Study of my Maven Pizza Sales Dashboard

Power Query for Maven Pizza Sales Dashboard (peep the Applied steps on the right).

Introduction:

In October 2022, I participated in the Maven Pizza Challenge, creating a dashboard to analyze sales data. Despite not winning the challenge, I noticed significant performance issues with my dashboard, particularly slow loading times and a heavy report size. I looked back at this old dashboard of mine and decided to optimize it using power query to enhance its efficiency.

This article details the steps I took to simplify Power Query processes, significantly improving performance without changing the visual elements of the dashboard. Join me as I share these insights, hoping to help you optimize your Power BI projects.

The Importance of Optimization:

Optimizing Power Query is essential for improving the performance of Power BI dashboards. Effective optimization provides several benefits:

• Faster Response Times: Improves user interaction by speeding up dashboard responsiveness, making it more user-friendly and engaging.
• Reduced Resource Consumption: This lowers the load on your system RAM and improves system efficiency by using fewer resources.
• Enhanced Data Quality: Streamlines data management processes, which leads to more reliable and accurate data presentations.
• Increased User Confidence and Adoption: An optimized dashboard builds trust among users, encouraging them to rely on the dashboard for regular decision-making due to its accuracy and responsiveness.

Identifying the Bottlenecks:

To kickstart the optimization process, I made a detailed assessment of the existing power Query setup. I discovered several key areas contributing to the dashboard’s inefficiency:

• Excessive Data Loading: The dashboard was initially configured to load large datasets in full at startup, consuming substantial system resources and resulting in slow load times.

Dashboard taking time to load.

• Redundant Calculations: Multiple Power Query steps across different queries were performing the same calculations, creating unnecessary computational overhead and slowing down the dashboard.
• Presence of Unused Data Columns: Numerous columns that were unused in the final reports or visualizations were still being processed through the transformation steps, wasting resources and degrading performance.

‘pizzas’ table before optimization processes.

• Inefficient Query Design: The structure and design of the queries were not optimized for performance. To reduce the workload on Power BI, steps needed to be consolidated and unnecessary steps removed from the process.

Technical Walkthrough:

Step 1: Consolidating Applied Steps

I noticed that multiple queries were performing redundant transformations, such as filtering, removing columns and merging operations. This redundancy led to unnecessary complexity and increased load times. So, I “consolidated” similar steps into a single query wherever possible. This means that I tool all the many steps that involved similar operations, and I “squeezed” them into one step.

For example, instead of separately removing columns throughout your data cleaning process, you can just put all that in one step and remove all the unnecessary columns at the same time.

‘pizzas’ table after consolidating applied steps.

Step 2: Pruning Unnecessary Columns

During the review of the data model, it was obvious that several columns that were loaded through Power Query were not used in any of my final visualizations. These columns were consuming memory and processing power and had to me removed… early! I took note of the unused columns and removed them from my queries early in the transformation process.

By reducing the volume of data processed and stored during each session of my dashboard, I noticed a lot of improvement in data refresh rates and a reduction in memory usage.

Step 3: Optimizing Data Load before Startup

The initial load of the dashboard was slow due to the start of data processing. I decided to pre-process some of the data cleaning/transformation using Microsoft Excel before loading to Power Query. This way, the dashboard loads data that has already been pre-calculated, reducing the computation tasks required at startup.

Step 4: Disabling Loading of Unused Tables

After merging columns and consolidating data, several tables were no longer necessary for the dashboard’s functionality. So, I merged necessary columns from those tables into other tables, and then disabled the loading of the (now-unused) tables.

This did two things: first it cleaned up my data model so that I didn’t have more than necessary tables in my Data panel, and second, it reduced the loading time and memory usage — since the dashboard no longer needed to process these unused tables.

Results of my Optimization:

• Load Time Reduction: Initially, the dashboard took approximately 35 seconds to load. After optimization, this time was reduced to about 19 seconds, a 46% decrease in load time. This improvement was particularly noticeable during the dashboard’s startup phase and when performing data-intensive operations.
• Data Processing Efficiency: Refreshing data and running reports are now 40% faster. I achieve this enhancement when I reduced the amount of data loaded, and consolidated transformation steps in Power Query.
• Low Resource Consumption: By eliminating unnecessary data and streamlining our queries, we’ve reduced the dashboard’s demand on system resources by approximately 40%. This not only makes the dashboard quicker but also lighter, putting less strain on my hardware.

• Optimized Data Load at Startup: Because I pre-processed certain data elements using Microsoft Excel (you can use Google Sheets if you want), the initial loading phase was essentially sped up, offering a quicker start and an improved user experience.

Conclusion:

The journey to optimize my Maven Pizza Sales Dashboard was a rewarding experience that significantly improved its performance. I cut down on the dashboard’s loading time from a lengthy 25 seconds to a swift 19 seconds, enhancing its usability. This journey not only improved the dashboard’s performance but also increased the appreciation of its users.

Best Practices:

• Embrace Regular Check-ups: Just like any well-oiled machine, our BI tools thrive on regular maintenance. It keeps them running smoothly and efficiently.
• Keep It Simple: The magic often lies in simplicity. By focusing on essential data and streamlining processes, we can unlock significant performance gains.
• Stay Proactive: Don’t wait for a system to break down. Regular optimizations can lead to better tools and happier users.

Dashboard Images:

Dashboard BEFORE optimization.

Dashboard AFTER optimization.

P.S: I also made some visual changes to the dashboard which are not covered in this article, as this article only talks about my optimization activities using Power Query.

You can access the [UPDATED] dashboard on Power BI Service here!