Stop Using Power Query Editor — Add SQL Queries Directly in Excel Instead

The first thing most people do when they need to pull data from SQL Server or Access is open Power Query Editor, click through six screens, rename columns manually, and then wonder why their refresh fails silently. That’s overkill — and it hides the actual SQL logic you wrote. Worse, it makes debugging a nightmare because Excel buries your WHERE clause inside nested M code you didn’t write.

Quick Answer

You don’t need Power Query to run SQL in Excel. Use Data > Get Data > From Database > From SQL Server Database (or From Microsoft Access Database), then paste your full SELECT statement directly into the Native Database Query box — no M code required. For existing connections, right-click the query table > Edit > Advanced Editor, and replace the M step with Value.NativeQuery() pointing to your SQL string.

All the Methods

Method	Steps	Best For	Limitations
Native Database Query (SQL Server/Access)	Data > Get Data > From Database > Choose source > Enter credentials > Paste SQL in 'SQL Statement' box	One-time reports, ad-hoc analysis, legacy Access DBs	No parameters without Power Query; requires driver install
Value.NativeQuery() in Power Query	Create blank query > Advanced Editor > Replace content with `let Source = Sql.Database("server", "db"), Query = Value.NativeQuery(Source, "SELECT * FROM Sales WHERE Year = 2024") in Query`	Dynamic filtering, reusability, parameterized queries	Requires M knowledge; won’t work with ODBC drivers that block direct execution
ODBC + Microsoft Query (Legacy)	Data > From Other Sources > From Microsoft Query > Choose DSN > Click 'SQL' button > Paste query	Older Excel versions, corporate environments with strict DSN policies	UI disappears in Excel 365; no support for subqueries or CTEs in some drivers
ADO via VBA	Insert module > Write ConnectionString + CommandText > Execute > Paste results to Range (e.g., Sheets("Output").Range("A1"))	Automated dashboards, scheduled refreshes, user-triggered reports	Macro security warnings; breaks if server name changes; not visible in Data tab

Method 1 Deep Dive

Let’s say you’re pulling Q1 sales from Acme Corp’s SQL Server. You want all orders where Status = 'Shipped' and OrderDate >= '2024-01-01'. Don’t build this in Power Query step-by-step. Go straight to the source. Open Excel → Data tab → Get Data → From Database → From SQL Server Database. Enter:
Server: prod-sql-01.internal
Database: SalesAnalytics Click OK. When the Navigator appears, **don’t select any tables**. Instead, click “Advanced options” at the bottom left. A small dialog opens — that’s your golden gate. Paste this exact SQL:

SELECT OrderID, CustomerName, ProductCode, Amount, OrderDate 
FROM dbo.Orders 
WHERE Status = 'Shipped' AND OrderDate >= '2024-01-01' AND OrderDate < '2024-04-01'

Click OK → Load. Your result lands in Sheet1 starting at A1. The beauty of this approach? Every time you refresh (Ctrl+Alt+F5), Excel re-executes *that exact query* — no hidden M transformations rewriting your date logic. And if your query fails? The error message shows line 3, column 22 — not ‘Expression.Error: We couldn’t parse the input.’ Here’s the counterintuitive part: If you later need to change the date range, **don’t edit the query in Power Query Editor**. Right-click the output table → “Edit Query” → then immediately click the gear icon next to “Source” in the Applied Steps pane. That opens the Native Query editor — clean, editable, SQL-only. No M clutter.

Method 2 Deep Dive

What if you need the same query to accept a dynamic year? That’s where Value.NativeQuery() shines — and where most people get stuck. Start with a blank query: Data → Get Data → From Other Sources → Blank Query. In the formula bar, paste:

let
    Source = Sql.Database("prod-sql-01.internal", "SalesAnalytics"),
    YearParam = 2024,
    Query = Value.NativeQuery(
        Source, 
        "SELECT CustomerName, SUM(Amount) AS TotalSales 
         FROM dbo.Orders 
         WHERE YEAR(OrderDate) = " & Number.ToText(YearParam) & " 
         GROUP BY CustomerName 
         ORDER BY TotalSales DESC",
        [EnableFolding=true]
    )
in
    Query

Press Enter. You’ll see 7 rows — exactly matching this sample output:

CustomerName	TotalSales
Sarah Chen	$142,850.00
Rajiv Mehta	$98,320.50
Lena Dubois	$87,105.25
Kenji Tanaka	$76,440.00
Amina Patel	$63,912.75
Diego Morales	$55,200.00
Nina Okoro	$49,650.30

Notice the [EnableFolding=true] argument. That tells Power Query to push the entire operation to SQL Server — not pull all rows then filter in Excel. Without it, you’d pull 2M rows just to sum seven customers. Big difference. To make YearParam editable: Go to Data → Queries & Connections → right-click your query → Properties → check “Enable load to worksheet” and “Refresh data when opening file”. Then create a cell (say, Sheet2!B2) with value 2024. Back in Power Query, replace YearParam = 2024 with YearParam = Excel.CurrentWorkbook(){[Name="YearInput"]}[Content]{0}[Column1], where “YearInput” is the named range covering Sheet2!B2.

Cheat Sheet

Action	How To	Shortcut
Open SQL Server import wizard	Data tab → Get Data → From Database → From SQL Server Database	None — but use Alt+A, Y, D, S in sequence once on Data tab
Edit native SQL of existing query	Right-click table → Edit Queries → Applied Steps → click gear icon next to “Source”	Alt+F3 (opens Advanced Editor instantly)
Refresh all SQL-based queries	Data tab → Refresh All (or right-click any query table → Refresh)	Ctrl+Alt+F5
Paste SQL into Microsoft Query (legacy)	Data → From Other Sources → From Microsoft Query → Choose DSN → click SQL button → paste	Alt+D, Q, S, Q (then Alt+S in SQL window)
Force SQL folding in NativeQuery	Always include `[EnableFolding=true]` as third argument in Value.NativeQuery()	None — but omitting it is the #1 cause of slow refreshes