Query folding in Power Query in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst working with Power BI to prepare sales data for reporting.

📋 Request: Your manager wants you to optimize data loading by using query folding in Power Query to improve performance and reduce data refresh time.

📊 Data: You have a sales database table with columns: Date, Region, Product, SalesAmount, and Quantity. The data is large and stored in a SQL Server database.

🎯 Deliverable: Create a Power Query query that filters sales for the current year and sums SalesAmount by Region, ensuring query folding is applied to push transformations to the database.

Sample Data

Date	Region	Product	SalesAmount	Quantity
2024-01-05	North	Widget A	1200	10
2024-02-15	East	Widget B	850	7
2023-12-20	South	Widget A	950	8
2024-03-10	West	Widget C	1300	12
2024-01-25	North	Widget B	700	5
2023-11-30	East	Widget C	600	4
2024-04-05	South	Widget B	1100	9
2024-02-20	West	Widget A	900	6

1

Step 1: Connect Power Query to the SQL Server database containing the sales table.

In Power BI Desktop, go to Home > Get Data > SQL Server, enter server and database details, then select the sales table.

Expected Result

Sales table data is loaded into Power Query.

2

Step 2: Filter the sales data to include only rows where the Date is in the current year (2024).

Add a filter step in Power Query: Date column >= #date(2024, 1, 1) and Date column <= #date(2024, 12, 31).

Expected Result

Only sales records from 2024 remain in the query.

3

Step 3: Group the filtered data by Region and sum the SalesAmount for each region.

Use Group By in Power Query: Group by Region, aggregate SalesAmount with Sum.

Expected Result

A table with Region and total SalesAmount for 2024.

4

Step 4: Verify that query folding is active to push filtering and grouping to the SQL Server database.

Right-click the last step in Power Query > View Native Query (should be enabled).

Expected Result

Native SQL query is shown, confirming query folding.

5

Step 5: Load the optimized query result into Power BI for reporting.

Click Close & Apply in Power Query Editor.

Expected Result

Aggregated sales by region for 2024 are loaded into Power BI model.

Final Result

Region   | Total SalesAmount
---------|------------------
North    | 1900
East     | 850
West     | 2200
South    | 1100

✓North region has total sales of 1900 in 2024.

✓West region leads with the highest sales amount of 2200 in 2024.

✓Query folding pushed filtering and aggregation to the database, improving performance.

Bonus Challenge

Modify the query to also calculate the total Quantity sold by Region for 2024, maintaining query folding.

Show Hint

Add Quantity as an additional aggregation in the Group By step in Power Query.