SQL Server & Relational Databases
Databases are where serious organisational data lives, and connecting to one is easy. Doing it well is not — the difference between a query that pushes work down to the server and one that drags a hundred million rows across the network is a single decision you make in the first ninety seconds.
Learning Objective
The critical concept here is query folding. Get it right and a database source is fast and scalable; get it wrong and it is slower than a CSV.
- Connect to SQL Server and other relational databases with the right authentication
- Choose appropriately between tables, views and native queries
- Explain query folding and check whether a step still folds
- Name the operations that break folding, and work around them
- Decide when a database view is the better answer than Power Query logic
Why This Matters
Two teams connect to the same 200-million-row sales table.
Team A selects the table, then filters to the last two years in Power Query. The filter folds — Power Query rewrites it as a SQL WHERE clause, the server does the filtering, and 4 million rows cross the network. Refresh takes two minutes.
Team B writes a native SQL query with a custom transformation in it, then filters afterwards. Folding is broken from the first step, so all 200 million rows are pulled across the network and filtered locally. Refresh takes ninety minutes, when it does not time out.
Concept
Connecting
Home › Get data › SQL Server database. The dialog asks for three things and hides a fourth.
| Field | What to enter | Notes |
|---|---|---|
| Server | Server name, or server\instance, or server,port | Ask your DBA. Do not guess |
| Database | Optional — leave blank to browse all you can see | Filling it in makes the query more explicit and slightly faster to navigate |
| Data Connectivity mode | Import or DirectQuery | The most consequential choice in the dialog. Lesson 4 covers it in full |
| Advanced options (collapsed) | Command timeout, and a native SQL statement | The SQL box here is the folding trap — see below |
Authentication
| Method | When | Refresh in the service |
|---|---|---|
| Windows | Your domain account has database access | Needs a gateway with stored credentials |
| Database | A SQL login and password | Needs a gateway; credentials stored in the service |
| Microsoft account / OAuth | Azure SQL with Entra ID authentication | Cloud-to-cloud, so often no gateway needed |
Tables, views and native queries
| Approach | How | Folding | Verdict |
|---|---|---|---|
| Select a table | Tick it in the Navigator | Full — every subsequent step can fold | Default choice. Shape in Power Query and let it fold |
| Select a view | Tick it in the Navigator | Full — a view is a table to Power Query | Excellent when a DBA can create one. Logic lives with the data |
| Native SQL query | Advanced options → SQL statement | None — folding is dead from step one | Last resort. Only when the logic genuinely cannot be expressed otherwise |
This surprises people because a native query feels more efficient. It is the opposite.
Query folding
Query folding is Power Query translating your Applied Steps into a single native query the source executes. Instead of "fetch everything, then filter here", it becomes "ask the server for exactly what is needed".
How to check whether a step folds
Right-click any step in Applied Steps and look for View Native Query:
- Enabled — the step folds. You can see the SQL that will run.
- Greyed out — folding has stopped at or before this step.
Work down the steps and find the first one where it greys out. That is where folding broke, and that is the step to reconsider.
What folds and what does not
| Usually folds | Usually breaks folding |
|---|---|
| Filter rows | Adding an index column |
| Remove / select columns | Merging with a query from a different source |
| Rename columns | Most custom columns using M-only functions |
| Change type | Table.Buffer |
| Group By | Anything after a native SQL statement |
| Merge with another table on the same source | Some fuzzy-matching operations |
| Sort | Certain date and text functions with no SQL equivalent |
| Remove duplicates | Adding a column from examples, in some cases |
Filter and remove columns at the very top of your query. Add index columns and exotic custom columns at the bottom. That way the server does the heavy reduction, and the small remaining result is processed locally. Reordering steps is often the entire fix for a slow query.
When to ask for a view instead
If your Power Query logic against a database is getting complicated — several joins, business rules, unions — consider asking a DBA for a view.
| Power Query logic | Database view |
|---|---|
| Lives in the .pbix, invisible to anyone else | Lives with the data, discoverable and documented |
| Reimplemented in every report that needs it | Reused by every report, and by other tools |
| Folds only if every step folds | Always folds — it is a table |
| You control it | Requires a DBA, and a change request |
| Fine for report-specific shaping | Right for shared business logic |
Visual Explanation
The same requirement, three ways — and what each costs.
| Approach | Rows over the network | Where filtering happens | Refresh |
|---|---|---|---|
| Select table → filter in Power Query (folds) | ~4M | SQL Server | Fast |
| Native SQL with the filter written in | ~4M | SQL Server | Fast, but nothing after it folds |
| Native SQL without the filter → filter in Power Query | 200M | Your machine | Very slow |
| Select table → add index column → filter | 200M | Your machine | Very slow — the index broke folding |
What folding actually produces
Select a table, filter to 2025, keep four columns, and group by channel. With folding intact, View Native Query shows roughly this being sent to the server:
select [Channel],
sum([NetSales]) as [Total Sales],
count(*) as [Line Count]
from [dbo].[FactSales]
where [OrderDate] >= '2025-01-01'
and [OrderDate] < '2026-01-01'
group by [Channel]Three rows come back instead of several million. You wrote no SQL — you clicked filter, choose columns and group by, and Power Query wrote that for you.
Power BI Demonstration
Example Dataset
This lesson is conceptual and needs no download — it uses the sample data shown inline above. The next lesson that requires a file will link it here.
You can still browse every dataset in the course from the dataset library.
Step-by-Step Exercise
If you have a database available, work through the folding drill below. If you do not, do the written parts — the reasoning is the transferable skill.
Expected Result
| Observation | Expected |
|---|---|
| View Native Query on a CSV source | Always greyed out — file connectors do not fold |
| Filter on a folding SQL source | Appears as a WHERE clause in the native query |
| Choose Columns on a folding source | Shrinks the SELECT list |
| Group By on a folding source | Becomes GROUP BY with aggregates |
| Index column | Breaks folding — every later step runs locally |
| Refresh time with index early vs late | Noticeably slower with it early, on any sizeable table |
Common Mistakes
Fix: Select the table and shape in Power Query. If the SQL is genuinely necessary, put everything in it including filters and aggregation, so at least the server does all the work.
Fix: Add index columns as the very last step. Check View Native Query before and after to confirm.
Fix: Make View Native Query a routine check on every database query. It takes five seconds.
Fix: Confirm who needs access before building, and note the requirement in the README. For service refresh, plan for a gateway and a service account.
Fix: Ask for a database view, or build a dataflow. One definition, reused. This is exactly what shared semantic layers are for.
Professional Tip
Those two steps are where nearly all the performance is. Reducing 200 million rows to 4 million and 40 columns to 8 before anything else happens means every subsequent step operates on a fraction of the data — and if it folds, the server does even that work for you.
The five seconds spent confirming the fold is the highest-return check in this module.
Two further points:
- Set a command timeout for large sources. In Advanced options, the default timeout can be too short for a heavy initial load. Setting it explicitly beats discovering the limit at 2am during a scheduled refresh.
- Talk to your DBA early. They know which tables are indexed, which views already exist, and which queries will hurt the server. Ten minutes of conversation regularly saves a day of work, and it makes you the analyst they are willing to help next time.
Knowledge Check
Knowledge Check
5 questions. Answer each one to see why the right answer is right.
What is query folding?
How do you check whether a Power Query step still folds?
You paste a SQL statement into the connection dialog's Advanced options, then add a filter step in Power Query. Where does the filtering happen?
Where should an index column go in a database query?
Your Power Query logic against a database has grown to six joins and several business rules, and three other reports need the same logic. What is the better answer?
Practical Challenge
Write a query folding audit and remediation plan
You inherit a Power BI report against a 60-million-row SQL Server sales table. Refresh takes 50 minutes. The Applied Steps are listed below, in order. Produce an audit and a remediation plan.
- Mark each step FOLDS or BREAKS
- Identify the first step where folding breaks
- Propose a reordered step list that maximises folding
- State which steps cannot be moved, and why
- Estimate where the time is going in the current version
- Name one change outside Power Query that would help more than any reordering
1 Source — SQL Server, selected dbo.FactSales (table, not a query)
2 Added Index — Index column from 1
3 Filtered Rows — OrderDate >= 2023-01-01
4 Removed Columns — dropped 22 of 34 columns
5 Changed Type — set types on 12 columns
6 Merged Queries — joined to dbo.DimProduct (same server)
7 Expanded DimProduct — brought in Category, Subcategory
8 Added Custom Column — Margin = [NetSales] - [COGS]
9 Added Conditional Column — Size Band from [Quantity]
10 Grouped Rows — by Category, Channel, month
11 Sorted Rows — by Category ascendingTwo things to work out:
- Which single step is doing the damage, and where is it in the list?
- Of the steps that break folding, which genuinely need to run after the reduction, and which are only there by accident of authoring order?
For the last requirement, think about what a 60-million-row table needs that Power Query cannot provide.
Solution
Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.
The audit
| Step | Folds? | Note | |
|---|---|---|---|
| 1 | Source (table) | FOLDS | Selecting a table, not a native query — the right start |
| 2 | Added Index | BREAKS | This is the fault. Folding ends here, at step 2 of 11 |
| 3 | Filtered Rows | would fold | Runs locally because of step 2 |
| 4 | Removed Columns | would fold | Runs locally |
| 5 | Changed Type | would fold | Runs locally |
| 6 | Merged Queries (same server) | would fold | Same-source merges fold — this would have become a SQL JOIN |
| 7 | Expanded DimProduct | would fold | Part of the join |
| 8 | Added Custom Column (arithmetic) | would fold | Simple arithmetic translates to SQL |
| 9 | Added Conditional Column | usually folds | Becomes a CASE expression in most connectors |
| 10 | Grouped Rows | would fold | Becomes GROUP BY |
| 11 | Sorted Rows | would fold | Becomes ORDER BY |
The index column is almost certainly a leftover from someone debugging.
Reordered step list
1 Source — SQL Server, dbo.FactSales (table) FOLDS
2 Filtered Rows — OrderDate >= 2023-01-01 FOLDS
3 Removed Columns — keep only the 12 needed FOLDS
4 Changed Type FOLDS
5 Merged Queries — dbo.DimProduct (same server) FOLDS -> SQL JOIN
6 Expanded DimProduct FOLDS
7 Added Custom Column — Margin FOLDS
8 Added Conditional Column — Size Band FOLDS (CASE)
9 Grouped Rows — Category, Channel, month FOLDS -> GROUP BY
10 Sorted Rows FOLDS -> ORDER BY
11 Added Index — moved to last, on the grouped result breaks, harmlesslyThe index column now runs against a few hundred grouped rows instead of 60 million. It still breaks folding, but there is nothing left to fold.
Steps that cannot move
- Sorted Rows (step 10) must stay after the group-by — sorting before aggregation would be pointless work.
- Expanded DimProduct must immediately follow the merge; it is part of the same operation.
- Added Index can only move to the end if nothing depends on it. Check first: if a later step or a DAX measure references the index values, moving it changes them. Here it appears unused, which is why it is safe to move — but that has to be verified, not assumed.
Where the time is going
| Phase | Current | After remediation |
|---|---|---|
| Rows crossing the network | ~60,000,000 | ~0 (aggregation happens server-side) |
| Columns crossing the network | 34 | 12, then aggregated away |
| Join executed | In Power Query, locally, on 60M rows | On the server, as a SQL JOIN |
| Group By executed | Locally, on 60M rows | On the server, as GROUP BY |
| Rows finally loaded | A few hundred | A few hundred |
The report ends up with the same few hundred rows either way. Currently it transfers and processes 60 million to get there.
The change outside Power Query
The better answer: ask the DBA for a view over the aggregation, or — for a table this size — a materialised summary table refreshed by an overnight job. The report then reads a few hundred pre-aggregated rows and refresh becomes near-instant.
Secondarily, confirm there is an index on
OrderDate. If the filter is causing a full table scan, that alone may be most of the remaining time — and it is a one-line change for the DBA.Knowing the limits of your own layer — and being willing to say "this belongs upstream" — is what separates someone who optimises queries from someone who architects reporting.
Lesson Summary
- Query folding is Power Query translating your steps into a native query the source runs. It is the single biggest performance factor on database sources.
- Check it by right-clicking a step → View Native Query. Greyed out means folding has stopped.
- Select a table or view, not a native SQL query. Native SQL kills folding from step one.
- Filter and remove columns first. Put folding-breaking steps — index columns, exotic custom columns — last.
- Same-source merges fold into SQL JOINs; Group By folds into GROUP BY.
- Credentials are per user, per machine. Service refresh needs a gateway for on-premises sources.
- When logic is shared across reports, ask for a database view or a dataflow rather than duplicating it.
- Know when a problem belongs upstream — some things no amount of Power Query tuning will fix.
Syllabus topics delivered
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.