Home Course home Curriculum Learner dashboard Skill tree Business projects Datasets Final project
🔌 Module 2 · Connecting to Data Intermediate LESSON 13 / 72

SQL Server & Relational Databases

30 min 5 quiz questions 1 practical challenge 4 syllabus topics

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.

Lesson 13 of 72 · Connecting to Data 18%
01

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.

By the end of this lesson you will be able to
  • 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
02

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.

Same data, same filter, same result — 45× the time
Nothing in the interface warns Team B. Their query is not wrong; it produces the correct answer. It is simply doing the work in the worst possible place. Query folding is invisible until you go looking for it, which is why this lesson exists.
03

Concept

Connecting

Home › Get data › SQL Server database. The dialog asks for three things and hides a fourth.

The connection dialog
FieldWhat to enterNotes
ServerServer name, or server\instance, or server,portAsk your DBA. Do not guess
DatabaseOptional — leave blank to browse all you can seeFilling it in makes the query more explicit and slightly faster to navigate
Data Connectivity modeImport or DirectQueryThe most consequential choice in the dialog. Lesson 4 covers it in full
Advanced options (collapsed)Command timeout, and a native SQL statementThe SQL box here is the folding trap — see below

Authentication

MethodWhenRefresh in the service
WindowsYour domain account has database accessNeeds a gateway with stored credentials
DatabaseA SQL login and passwordNeeds a gateway; credentials stored in the service
Microsoft account / OAuthAzure SQL with Entra ID authenticationCloud-to-cloud, so often no gateway needed
Credentials are stored per user, per machine in Desktop, and separately in the service. A .pbix that refreshes on your laptop will still prompt a colleague for their own credentials — and if they lack database access, no amount of file preparation helps. Establish who has access before you build.

Tables, views and native queries

Three ways to get data out
ApproachHowFoldingVerdict
Select a tableTick it in the NavigatorFull — every subsequent step can foldDefault choice. Shape in Power Query and let it fold
Select a viewTick it in the NavigatorFull — a view is a table to Power QueryExcellent when a DBA can create one. Logic lives with the data
Native SQL queryAdvanced options → SQL statementNone — folding is dead from step oneLast resort. Only when the logic genuinely cannot be expressed otherwise
Why a native query kills folding
Power Query folds by rewriting your steps into SQL. If you have already handed it opaque SQL text, it has nothing to rewrite — it can only run your statement and process the result locally. Everything after that first step happens on your machine.

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

Common operations
Usually foldsUsually breaks folding
Filter rowsAdding an index column
Remove / select columnsMerging with a query from a different source
Rename columnsMost custom columns using M-only functions
Change typeTable.Buffer
Group ByAnything after a native SQL statement
Merge with another table on the same sourceSome fuzzy-matching operations
SortCertain date and text functions with no SQL equivalent
Remove duplicatesAdding a column from examples, in some cases
The ordering rule
Put every folding step first, and every non-folding step last.

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 logicDatabase view
Lives in the .pbix, invisible to anyone elseLives with the data, discoverable and documented
Reimplemented in every report that needs itReused by every report, and by other tools
Folds only if every step foldsAlways folds — it is a table
You control itRequires a DBA, and a change request
Fine for report-specific shapingRight for shared business logic
04

Visual Explanation

The same requirement, three ways — and what each costs.

200M-row table, two years needed
ApproachRows over the networkWhere filtering happensRefresh
Select table → filter in Power Query (folds)~4MSQL ServerFast
Native SQL with the filter written in~4MSQL ServerFast, but nothing after it folds
Native SQL without the filter → filter in Power Query200MYour machineVery slow
Select table → add index column → filter200MYour machineVery slow — the index broke folding
The fourth row is the one that catches people
Adding an index column early feels harmless. It silently ends folding, so the filter beneath it can no longer be pushed to the server, and suddenly you are pulling the whole table. Move the index step to the end and folding resumes for everything above it.

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:

SQL · Generated by query folding
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.

The exact SQL Power Query generates varies by connector and version, and is often less tidy than the example above — expect nested subqueries. The point is what gets pushed down, not the formatting.
05

Power BI Demonstration

Connect to a database
Home › Get data › SQL Server database. Enter the server. Leave Database blank to browse. Choose Import for now.
Authenticate
Pick the method your organisation uses. If unsure, try Windows first — it is the most common on internal servers.
Select a table, not a query
In the Navigator, tick a table. Choose Transform Data.
Check that folding is on
Right-click the Source step → View Native Query. It should be available and show a simple select.
Add a filter and check again
Filter a date column to a recent range. Right-click the new step → View Native Query. The WHERE clause should now be in the SQL.
Remove columns and check again
Use Choose Columns to keep a handful. View Native Query — the select list should have shrunk.
Break folding deliberately
Add Column › Index Column › From 1. Right-click that step → View Native Query. It is now greyed out.
Add another filter below it and observe
Filter something else. View Native Query on that step — still greyed. Everything after the index runs locally.
Fix it by reordering
Drag the index step to the bottom of Applied Steps (or delete and re-add it last). Check the filter step again — folding is restored.
Compare a native query
Start a new query, this time pasting SQL into Advanced options. Right-click Source → View Native Query. It shows your SQL. Now add a filter step and check — greyed out immediately.
Applied Stepsright-click a stepView Native Query
No database to practise on?
The folding concept still matters and you can learn the mechanics without one. Microsoft publishes free sample databases (AdventureWorks, WideWorldImporters) that run on SQL Server Express or Azure SQL's free tier. Alternatively, note that View Native Query is greyed out for every step on a CSV source — file connectors do not fold at all, which is itself a useful thing to see.
06

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.

07

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.

Build a folding query
Connect to any table. Filter rows, remove columns, and group by something. Confirm each step still shows View Native Query.
Record the generated SQL
Copy the SQL from the final step. Note how much of your Power Query work it expresses.
Break it and measure
Insert an index column near the top. Refresh and note the time. Move the index to the end. Refresh again and note the time.
Write the folding audit
For a query of your own, list every step and mark it FOLDS or BREAKS. Identify the first break.
Propose a reordering
Rewrite the step order so every folding step precedes every non-folding one. Note which steps could not be moved and why.
Decide the view question
For one query, write two sentences arguing whether its logic belongs in Power Query or in a database view.
08

Expected Result

Expected findings
ObservationExpected
View Native Query on a CSV sourceAlways greyed out — file connectors do not fold
Filter on a folding SQL sourceAppears as a WHERE clause in the native query
Choose Columns on a folding sourceShrinks the SELECT list
Group By on a folding sourceBecomes GROUP BY with aggregates
Index columnBreaks folding — every later step runs locally
Refresh time with index early vs lateNoticeably slower with it early, on any sizeable table
The audit is the deliverable
Being able to look at a query and say "folding breaks at step 4, and if we move the index column to the end it will fold through to step 9" is a genuinely valuable skill. It comes up in interviews for anything beyond junior level, and it is the difference between a report that scales and one that has to be rebuilt when the data grows.
09

Common Mistakes

Pasting SQL into the connection dialog by habit
Analysts who know SQL reach for it instinctively. It kills folding immediately, so every Power Query step afterwards runs locally.

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.
Adding an index column early in the query
It feels like a harmless helper. It silently ends folding for everything below it.

Fix: Add index columns as the very last step. Check View Native Query before and after to confirm.
Never checking whether folding happens at all
The query works, so nobody looks. It works slowly, and gets slower as the source grows.

Fix: Make View Native Query a routine check on every database query. It takes five seconds.
Assuming a colleague can refresh because you can
Credentials are per user. Your access does not travel with the file.

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.
Rebuilding the same joins in five different reports
The same business logic reimplemented in Power Query in every .pbix that needs it. They drift apart and start disagreeing.

Fix: Ask for a database view, or build a dataflow. One definition, reused. This is exactly what shared semantic layers are for.
10

Professional Tip

Filter first, always — and check the fold
The first two steps of every database query should be filter rows and remove columns. Then right-click and confirm View Native Query is still available.

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.
11

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?

12

Practical Challenge

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.

Requirements
  • 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
Reference · The inherited Applied Steps, in order
 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 ascending

Two things to work out:

  1. Which single step is doing the damage, and where is it in the list?
  2. 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.

13

Solution

Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.

The audit

StepFolds?Note
1Source (table)FOLDSSelecting a table, not a native query — the right start
2Added IndexBREAKSThis is the fault. Folding ends here, at step 2 of 11
3Filtered Rowswould foldRuns locally because of step 2
4Removed Columnswould foldRuns locally
5Changed Typewould foldRuns locally
6Merged Queries (same server)would foldSame-source merges fold — this would have become a SQL JOIN
7Expanded DimProductwould foldPart of the join
8Added Custom Column (arithmetic)would foldSimple arithmetic translates to SQL
9Added Conditional Columnusually foldsBecomes a CASE expression in most connectors
10Grouped Rowswould foldBecomes GROUP BY
11Sorted Rowswould foldBecomes ORDER BY
One step, ten consequences
Folding breaks at step 2. Every one of the nine steps after it — including the filter that would have cut the table down, and the group-by that would have reduced it to a few hundred rows — is executing on your machine, on all 60 million rows.

The index column is almost certainly a leftover from someone debugging.

Reordered step list

Reference · Remediated order
 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, harmlessly

The 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

PhaseCurrentAfter remediation
Rows crossing the network~60,000,000~0 (aggregation happens server-side)
Columns crossing the network3412, then aggregated away
Join executedIn Power Query, locally, on 60M rowsOn the server, as a SQL JOIN
Group By executedLocally, on 60M rowsOn the server, as GROUP BY
Rows finally loadedA few hundredA 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

Ask for an indexed view or a summary table
Reordering makes the SQL correct, but the server still has to scan 60 million rows and aggregate them on every refresh. If this report runs daily, that work is repeated daily for an identical result.

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.
The judgement being tested here
The reordering is the obvious answer and it is worth a large speed-up. The genuinely senior observation is the last one: that repeating the same expensive aggregation daily is a design problem no amount of Power Query tuning will fix, and that the right move is a conversation with the database team.

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.
14

Lesson Summary

Key takeaways
  • 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

SQL ServerRelational databasesAuthentication modesNative query trade-offs
15

Progress

Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.

Lesson complete — 18% of Power BI Mastery done
Progress saved. Keep the momentum: the next lesson builds directly on this one.