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.

Practice

1. What is query folding in Power Query?

easy

A. It pushes data transformations back to the data source for faster processing.

B. It copies all data locally before applying transformations.

C. It disables all data refresh options.

D. It automatically creates charts from data.

Solution

1. Step 1: Understand the concept of query folding

   Query folding means Power Query sends transformation steps back to the data source to run there.

2. Step 2: Compare options with this definition

   Only It pushes data transformations back to the data source for faster processing. describes pushing transformations to the source for speed.

3. Final Answer:

   It pushes data transformations back to the data source for faster processing. -> Option A

4. Quick Check:

   Query folding = pushing steps to source [OK]

Hint: Query folding means running steps at the source, not locally [OK]

Common Mistakes:

• Thinking query folding copies data locally first
• Confusing query folding with data visualization
• Believing query folding disables refresh

2. Which of the following Power Query steps supports query folding?

easy

A. Adding a custom column with complex M code referencing external files

B. Creating a manual index column

C. Merging queries with a local Excel table

D. Filtering rows based on a column value

Solution

1. Step 1: Identify steps that can be folded

   Simple filters on source columns usually fold back to the database.

2. Step 2: Analyze other options

   Custom columns with external references, merges with local tables, and manual indexes break folding.

3. Final Answer:

   Filtering rows based on a column value -> Option D

4. Quick Check:

   Filters fold, complex custom steps don't [OK]

Hint: Simple filters fold; complex or local steps usually don't [OK]

Common Mistakes:

• Assuming all steps fold automatically
• Thinking merges with local tables fold
• Believing manual indexes fold

3. Given this Power Query M code, which step breaks query folding?

let
  Source = Sql.Database("Server", "DB"),
  FilteredRows = Table.SelectRows(Source, each [Status] = "Active"),
  AddedCustom = Table.AddColumn(FilteredRows, "Custom", each Text.Length([Name]))
in
  AddedCustom

medium

A. Source step

B. FilteredRows step

C. AddedCustom step

D. None, all steps fold

Solution

1. Step 1: Check each step for folding support

   Source connects to SQL, supports folding. Filtering rows on a column also folds.

2. Step 2: Analyze the AddedCustom step

   Adding a custom column with a function like Text.Length is done locally and breaks folding.

3. Final Answer:

   AddedCustom step -> Option C

4. Quick Check:

   Custom columns break folding [OK]

Hint: Custom columns usually break folding; filters don't [OK]

Common Mistakes:

• Thinking all steps fold if source is SQL
• Assuming filters break folding
• Ignoring custom column impact

4. You notice your Power Query refresh is slow. Which fix helps restore query folding?

medium

A. Replace a custom column step with a simple filter step

B. Add a manual index column before filtering

C. Merge with a local Excel table before filtering

D. Disable the data source privacy settings

Solution

1. Step 1: Identify what breaks folding

   Custom columns and merges with local tables break folding and slow refresh.

2. Step 2: Choose a fix that restores folding

   Replacing custom columns with simple filters allows folding to resume.

3. Final Answer:

   Replace a custom column step with a simple filter step -> Option A

4. Quick Check:

   Simple filters restore folding, custom steps break it [OK]

Hint: Use filters instead of custom columns to keep folding [OK]

Common Mistakes:

• Adding manual indexes early breaks folding
• Merging with local tables breaks folding
• Thinking privacy settings affect folding

5. You want to optimize a Power Query that merges a SQL table with a large local Excel table. How can you maximize query folding?

hard

A. Load the local Excel table to SQL before merging

B. Filter the SQL table first, then merge with the local Excel table

C. Merge first, then filter the combined table

D. Disable query folding to avoid errors

Solution

1. Step 1: Understand folding with merges

   Merging with local tables breaks folding, but filtering source first keeps folding for that part.

2. Step 2: Choose the best approach

   Filtering SQL table before merging reduces data and preserves folding on the source side.

3. Final Answer:

   Filter the SQL table first, then merge with the local Excel table -> Option B

4. Quick Check:

   Filter source before merge to keep folding [OK]

Hint: Filter source data before merging with local tables [OK]

Common Mistakes:

• Merging before filtering breaks folding early
• Assuming disabling folding helps performance
• Not considering loading local data to SQL