Optimizing Performance

While returning accurate data is important, the speed of your queries greatly affects the user experience. Long refresh times for your datasets may result in timeouts, and waiting for the results of your transformations in the Power Query editor can be frustrating. In this chapter, we will delve into strategies to optimize the performance of your queries. We will first look at memory usage when evaluating queries and how using too much memory slows down your queries. We will then look at different strategies to prevent this from happening.

We’ll look at the importance of query folding and how you can ensure that the data source processes as many transformations as possible. Then, we’ll cover the formula firewall, a tool designed to protect your queries from exposing sensitive information. We’ll explain what triggers errors related to this and discuss its effect on your queries. Additionally, we’ll examine how storing data in memory...