Query folding in Power Query in Power BI - Dashboard Guide

Dashboard Mode - Query folding in Power Query

Goal

Understand how query folding improves data refresh speed by pushing transformations to the data source in Power Query.

Sample Data

OrderID	Customer	Region	OrderDate	Sales
1001	Alpha Co	East	2024-01-10	250
1002	Beta LLC	West	2024-02-15	450
1003	Gamma Inc	East	2024-03-20	300
1004	Delta Ltd	North	2024-04-05	500
1005	Epsilon	South	2024-05-12	150

Dashboard Components

• KPI Card: Total Sales
  Formula: = List.Sum(Source[Sales])
  Shows total sales amount from all orders.
• Table: Filtered Orders (Region = East)
  Formula: = Table.SelectRows(Source, each [Region] = "East")
  Shows only orders from the East region.
• Chart: Sales by Region
  Formula: = Table.Group(Source, {"Region"}, {{"TotalSales", each List.Sum([Sales]), type number}})
  Aggregates sales per region for visualization.
• Query Folding Indicator
  Shows if the applied filter and grouping steps are folded to the data source for efficiency.

Dashboard Layout

+----------------------+-----------------------+
|      Total Sales      |   Sales by Region     |
|      (KPI Card)       |       (Chart)         |
+----------------------+-----------------------+
|       Filtered Orders (Table)                 |
+----------------------------------------------+
|           Query Folding Indicator             |
+----------------------------------------------+

Interactivity

User can select a region filter. This filter updates the Filtered Orders table and the Sales by Region chart. The Total Sales KPI updates to show sales only for the selected region. Query folding status updates to show if the filter is pushed to the data source.

Self Check

If you add a filter for Region = "West", which components update and how?

• Total Sales KPI shows sum of sales for West region only (450).
• Filtered Orders table shows only orders from West region (OrderID 1002).
• Sales by Region chart updates to show sales only for West region.
• Query Folding Indicator shows if the filter step is folded to the source.

Key Result

Dashboard shows total sales, sales by region, filtered orders by region, and query folding status in Power Query.