Level 6 · Import from SQL Server with a query

Exports are a workaround. When the data lives in a database you can reach, Excel can connect to it directly, and Power Query handles the connection the same way it handles a CSV: a Source step, then your steps, then Close & Load and Refresh. This level is a quiz because the practice editor on this site can’t connect to a server; the steps below are what you do in Excel.

Connect to SQL Server

1. Data → Get Data → From Database → From SQL Server Database.
2. Server: the name the database team gives you, for example sql01.harborpine.local. Database: HarborPine.
3. Leave Advanced options closed to browse tables, or open it to type a SQL statement (next section).
4. Excel asks how to sign in: Windows (your company login) or Database (a user name and password the database team created). Excel remembers it for this source.
5. Without a statement, the Navigator lists every table. Tick the ones you need and click Transform Data.

The same menu has From Microsoft Access Database, From Oracle, From MySQL and more; the dialog changes, the idea doesn’t.

Filter on the server with a SQL statement

Picking order_items in the Navigator pulls all 30,338 lines. Aaron’s request is the reason to do it differently: write the question in SQL, and the server answers it before anything travels.

SELECT oi.order_item_id, o.order_id, CAST(o.order_date AS date) AS order_date,
       oi.product_id, oi.quantity, oi.unit_price
FROM dbo.orders AS o
JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
WHERE o.channel = 'marketplace'
  AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01';

Paste it into Advanced options → SQL statement. The server returns 1,718 lines from 702 orders. Three reasons this beats importing whole tables:

Reason	What it means here
Speed	The server is built to filter and join millions of rows; Excel is not. Less data travels, refresh is faster.
Control	WHERE, JOIN, GROUP BY, renaming with AS: you decide the exact shape of what arrives.
Security	You read only the rows and columns your statement asks for, not whole tables.

The first time a workbook runs a statement, Excel asks you to approve the native database query. Approve it; it only runs what you wrote.

The SQL in the connection can do any shaping SQL can: total quantity per product with GROUP BY and ORDER BY ... DESC, products over a price with WHERE, or a few columns renamed with AS. Our SQL track teaches exactly these: SELECT basics (columns and AS), Filtering rows (WHERE), Aggregates and GROUP BY, and JOINs. Everything you write there works in this box, apart from small dialect differences in SQL Server (TOP 10 instead of LIMIT 10).

Edit the query later

Next January the dates change. You don’t start over:

1. Data → Queries & Connections, double-click the query. The Power Query Editor opens.
2. In Applied Steps, click the gear icon next to Source.
3. The connection dialog opens again with your SQL. Change 2025 to 2026, OK.
4. Close & Load. Every step after Source runs again on the new rows.

In M, the Source step is one line:

Source = Sql.Database("sql01.harborpine.local", "HarborPine",
    [Query="SELECT oi.order_item_id, ... WHERE o.channel = 'marketplace' AND ..."])

SQL first, then Power Query steps

The two work together. Let SQL do the heavy filtering and joining on the server, then add the light steps you have practised in this chapter on top of the result: a custom column line_total = [quantity] * [unit_price], a month column, a merge with a small lookup sheet.

When you connect without a statement, Power Query tries to translate your steps into SQL itself, which is called query folding. Right-click a step and choose View Native Query: if it’s available, that step still runs on the server. Some steps (many custom columns, for example) break folding, and every step after them runs in Excel on the full download. A statement you write yourself removes the guesswork.

Common mistakes

• Loading whole tables “just in case”. The workbook gets slow, and the database team gets unhappy.
• Hard-coding a date everywhere. Keep it in one place: the WHERE of the Source statement.
• Rebuilding the query to change it. The gear next to Source reopens the dialog with your SQL.
• Sharing a workbook that uses your login. Others need their own access to refresh it.