Query folding in Power Query in Power BI - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is query folding in Power Query?

Query folding is when Power Query translates your data transformation steps into a single query that runs on the data source, like a database, to improve speed and efficiency.

beginner

Why is query folding important?

It makes data refresh faster by pushing work to the data source instead of doing it locally, saving time and computer resources.

intermediate

How can you tell if query folding is happening in Power Query?

Right-click a step in Power Query and choose 'View Native Query'. If this option is available and shows a query, folding is happening.

intermediate

Name one common action that can break query folding.

Adding an index column or using certain custom functions can stop query folding because these steps can’t be translated back to the data source.

intermediate

How can you improve query folding in your Power Query transformations?

Try to do filtering, grouping, and sorting early in the query steps and avoid transformations that Power Query can’t push to the data source.

What does query folding do in Power Query?

A Creates a backup of the query

B Runs transformations on the data source to speed up processing

C Deletes unnecessary data automatically

D Copies all data locally before transforming

Query folding pushes transformations to the data source, making processing faster.

Which step can break query folding?

A Adding an index column

B Sorting data

C Filtering rows

D Selecting columns

Adding an index column often breaks query folding because it can’t be translated to the data source.

How do you check if query folding is active for a step?

A Refresh the query

B Look for a green checkmark

C Check the data preview

D Right-click the step and select 'View Native Query'

The 'View Native Query' option shows the folded query if folding is active.

When should you apply filters to maximize query folding?

A At the end of the query

B In the middle of the query

C Early in the query steps

D Filters do not affect query folding

Applying filters early helps Power Query push them to the data source.

What is a benefit of query folding?

A Reduces load on your computer

B Slows down data refresh

C Uses more local memory

D Prevents data refresh

Query folding reduces local processing by using the data source to do work.

Explain what query folding is and why it matters in Power Query.

Think about how Power Query talks to databases to save time.

Describe how you can check if query folding is working and what can cause it to stop.

Look for the native query and think about complex steps.

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