TECH **Power BI: Folding vs Non-Folding & Query Performance – Zero-Fluff Study Guide**

Power BI: Folding vs Non-Folding & Query Performance – Zero-Fluff Study Guide

1. What This Is & Why It Matters

Folding in Power BI is the process where Power Query pushes operations (filters, joins, aggregations) back to the data source (SQL Server, Oracle, etc.) instead of loading raw data and processing it locally. Non-folding means Power BI pulls raw data and processes it in-memory.

Why this matters in production:
- Performance: A query that folds runs in milliseconds on a SQL Server but takes minutes (or crashes) if it pulls 10M rows into Power BI.
- Cost: Non-folding queries spike memory usage, leading to premium capacity throttling or failed refreshes.
- Scalability: A report that works fine with 10K rows fails in production with 10M rows if folding isn’t optimized.

Real-world scenario:
You inherit a Power BI report that takes 20 minutes to refresh and crashes when filtering. The data source is SQL Server. The original developer wrote M code that looks like this:

let
    Source = Sql.Database("server", "db"),
    Data = Source{[Schema="dbo",Item="Sales"]}[Data],
    Filtered = Table.SelectRows(Data, each [OrderDate] >= #date(2023,1,1))
in
    Filtered

This doesn’t fold—Power BI pulls all rows from Sales before filtering. Fixing this could cut refresh time to 2 seconds.

2. Core Concepts & Components

3. Step-by-Step: Fixing a Non-Folding Query

Prerequisites

• Power BI Desktop (latest version).
• Access to a SQL Server database (or any folding-capable source).
• A slow report with a non-folding query.

Task: Convert a Non-Folding Query to a Folding Query

Step 1: Identify the Problem

1. Open Power BI Desktop.
2. Go to Home > Transform Data to open Power Query.
3. Select the slow query.
4. Right-click the last step > View Native Query.
5. If it’s blank, the query doesn’t fold.
6. If it shows SQL, folding is working (but may still be inefficient).

Step 2: Rewrite the Query to Force Folding

Bad (Non-Folding) Example:

let
    Source = Sql.Database("server", "db"),
    Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
    Filtered = Table.SelectRows(Sales, each [OrderDate] >= #date(2023,1,1)),
    Grouped = Table.Group(Filtered, {"ProductID"}, {{"TotalSales", each List.Sum([Amount]), type number}})
in
    Grouped

Why it fails:
- Table.Group doesn’t fold in most SQL sources.
- Power BI pulls all rows before grouping.

Good (Folding) Example:

let
    Source = Sql.Database("server", "db"),
    Filtered = Sql.Database("server", "db", [Query="SELECT ProductID, SUM(Amount) AS TotalSales FROM dbo.Sales WHERE OrderDate >= '2023-01-01' GROUP BY ProductID"])
in
    Filtered

Why it works:
- The entire operation is pushed to SQL Server.
- Only the aggregated results are loaded into Power BI.

Step 3: Verify Folding

1. Right-click the last step > View Native Query.
2. You should see:
   sql
SELECT ProductID, SUM(Amount) AS TotalSales
FROM dbo.Sales
WHERE OrderDate >= '2023-01-01'
GROUP BY ProductID
3. If you see this, folding is working.

Step 4: Optimize Further (If Needed)

• Add indexes on OrderDate and ProductID in SQL Server.
• Use Table.SelectColumns early to reduce data transfer: powerquery let
Source = Sql.Database("server", "db"),
Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
SelectColumns = Table.SelectColumns(Sales, {"ProductID", "Amount", "OrderDate"}),
Filtered = Table.SelectRows(SelectColumns, each [OrderDate] >= #date(2023,1,1)),
Grouped = Table.Group(Filtered, {"ProductID"}, {{"TotalSales", each List.Sum([Amount]), type number}}) in
Grouped
• Table.SelectColumns folds in most sources.
• Table.SelectRows folds if the filter is simple.

Step 5: Test Performance

1. Refresh the query and check duration.
2. Use Query Diagnostics:
3. Go to Tools > Start Diagnostics.
4. Refresh the query.
5. Go to Tools > Stop Diagnostics.
6. Open the diagnostics log and look for:
   • Long "Evaluating" steps (non-folding).
   • High "Data Volume" values (raw data being pulled).

4. ? Production-Ready Best Practices

Performance

✅ Always check folding before publishing.
✅ Use View Native Query after every major step.
✅ Push complex logic to SQL (stored procedures, views).
✅ Avoid Table.Buffer unless you intentionally want to load data into memory.
✅ Use Table.SelectColumns early to reduce data transfer.

Cost Optimization

? Non-folding queries spike memory usage → higher premium capacity costs.
? DirectQuery reports with non-folding queries cause slow interactions → poor user experience.
? Pre-aggregate data in SQL instead of in Power BI.

Reliability & Maintainability

? Document folding assumptions (e.g., "This query folds because we use a SQL view").
? Use Power BI Dataflows for non-folding sources (e.g., Excel, Web API).
? Test with production-scale data (not just a sample).

Observability

? Monitor refresh durations in the Power BI Service.
? Set up alerts for failed refreshes.
? Use Query Diagnostics to identify bottlenecks.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus (PL-300)

Typical Question Patterns

1. "Which of these steps will prevent query folding?"
2. ✅ Table.Buffer
3. ✅ Table.Profile
4. ✅ Table.AddColumn with a custom function

5. ❌ Table.SelectRows with a simple filter

6. "You have a DirectQuery report with slow performance. What’s the most likely cause?"

7. ✅ Non-folding queries running on every interaction.

8. ❌ Too many visuals on the page.

9. "How can you check if a query is folding?"

10. ✅ Right-click a step > View Native Query.
11. ❌ Check the "Query Dependencies" pane.

Key ⚠️ Trap Distinctions

Common Scenario-Based Question

"You have a SQL Server table with 10M rows. A Power BI report takes 15 minutes to refresh. What’s the most likely fix?"
- ✅ Rewrite the query to fold (push filtering/aggregation to SQL).
- ❌ Increase the Power BI dataset size.
- ❌ Use Table.Buffer to speed up processing.

7. ? Hands-On Challenge (With Solution)

Challenge

You have this M query:

let
    Source = Sql.Database("server", "db"),
    Customers = Source{[Schema="dbo",Item="Customers"]}[Data],
    Orders = Source{[Schema="dbo",Item="Orders"]}[Data],
    Merged = Table.NestedJoin(Customers, "CustomerID", Orders, "CustomerID", "Orders", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "Orders", {"OrderID", "Amount"}, {"OrderID", "Amount"}),
    Filtered = Table.SelectRows(Expanded, each [Amount] > 1000)
in
    Filtered

Problem: It’s slow and doesn’t fold.
Task: Rewrite it to fold.

Solution

let
    Source = Sql.Database("server", "db", [Query="
        SELECT c.*, o.OrderID, o.Amount
        FROM dbo.Customers c
        LEFT JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
        WHERE o.Amount > 1000
    "])
in
    Source

Why it works:
- The entire join and filter is pushed to SQL Server.
- Only the final result is loaded into Power BI.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. Microsoft Docs: Query Folding in Power Query
2. PL-300 Exam Guide (Microsoft)
3. Power BI Performance Best Practices (Microsoft)
4. SQLBI: Understanding Query Folding

Final Tip:
Always ask: "Can the data source do this faster than Power BI?" If yes → fold it.
If no → pre-process in SQL or a dataflow.