Query folding in Power Query in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

What is Query Folding in Power Query?

Which of the following best describes query folding in Power Query?

A The process where Power Query translates steps into native queries executed by the data source.

B A method to manually write SQL queries inside Power Query editor.

C A feature that automatically compresses data files before loading.

D A way to create visual reports directly from Power Query.

💡 Hint

Think about how Power Query optimizes data retrieval from the source.

Query folding means Power Query sends transformations as native queries to the data source, improving performance by reducing data transfer.

📊 Formula Result

intermediate

Identifying Query Folding Status

You apply a filter step in Power Query on a SQL Server table. Which option correctly shows how to check if query folding is still active?

A Right-click the last step and select 'View Native Query'.

B Check the 'Applied Steps' pane for a green checkmark.

C Look for a 'Query Folding Enabled' message in the status bar.

D Open the Advanced Editor and search for 'fold'.

💡 Hint

Power Query provides a direct way to see the native query generated.

Right-clicking the last step and choosing 'View Native Query' shows the SQL query Power Query sends to the source, confirming folding.

❓ Function Choice

advanced

Which Power Query function breaks Query Folding?

Which of these Power Query functions will break query folding when applied?

A Table.Sort

B Table.SelectRows

C Table.RemoveColumns

D Table.TransformColumns with a custom M function

💡 Hint

Consider which function uses custom code that the data source cannot translate.

Using Table.TransformColumns with a custom M function often breaks folding because the data source cannot execute custom M code.

🎯 Scenario

advanced

Optimizing Query Folding in a Complex Query

You have a Power Query that merges two large tables and then adds a custom column with a complex M expression. Which approach will best preserve query folding?

A Avoid merging and instead append the tables.

B Perform the merge first, then add the custom column in a separate step after loading.

C Add the custom column inside the merge step using a native SQL query.

D Add the custom column before merging the tables.

💡 Hint

Think about when custom M code breaks folding and how to isolate it.

Performing the merge first preserves folding for that step. Adding the custom column after loading avoids breaking folding during the merge.

❓ data_analysis

expert

Determining Query Folding Impact on Performance

You notice your Power Query refresh is slow. You suspect query folding is broken. Which of the following observations best indicates that query folding is not happening?

A The query uses only built-in Power Query functions.

B The data preview loads instantly but the final load is slow.

C The query refresh time is much longer than expected and 'View Native Query' is disabled.

D The data source is a local Excel file.

💡 Hint

Consider what disables the 'View Native Query' option and how it relates to performance.

If 'View Native Query' is disabled, it means folding is broken. This usually causes slower refresh because all data is pulled before transformations.

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