Folding, joining, and aggregating

While Power Query has its own capable data shaping engine, it can push down certain transformations to data sources in their native query language. This is known as Query Folding, and formally, it means that the mashup engine can translate your transformation steps into a single SELECT statement that is sent to the data source.

Tip

Query Folding is an important concept as it can provide huge performance benefits. Folding minimizes the amount of data being returned to Power BI, and it can make a huge difference for refresh times or DirectQuery performance with large data volumes of many millions of rows.

There is a bit of knowledge and trial and error required to get the best folding setup. You know a query step is folded when you can right-click on it and see the View Native Query option enabled, as shown in the following screenshot:

Figure 8.8 – View Native Query indicates that folding has occurred

Ideally...