Native Query in Power BI: How to Write SQL Directly Against Your Data Source

Introduction

Most Power BI users connect to a database, pick a table, and let Power Query do the rest. That works fine — until it doesn't. Large tables, complex joins, slow refreshes, and existing SQL logic that already runs perfectly in your database can all push you toward a better approach: the native query.

A native query lets you write SQL directly inside Power BI and send it straight to your data source. The result is faster data loading, cleaner queries, and full control over what gets pulled in. This guide walks you through exactly how to do it — step by step.

---

Why Native Queries Exist — and When You Actually Need One

When you connect Power BI to a SQL Server, Power Query builds M code behind the scenes. In many cases, it also translates that M code back into SQL through a mechanism called query folding — pushing the work to the database where it belongs.

The problem is that query folding is not always guaranteed. Complex transformations, unsupported functions, or a simple merge step can silently break folding. When that happens, Power BI pulls the entire table into memory and filters it locally. On a 50-million-row table, that is a serious performance problem.

A native query sidesteps this entirely. Instead of letting Power Query guess what SQL to run, you write the SQL yourself and hand it directly to the database engine.

Use a native query when:

• Your database already has optimized SQL for a specific report

• You need complex joins, window functions, or CTEs that are awkward to build in the Power Query GUI

• You want to filter a large table at the source before anything is loaded into Power BI

• You need to use database-specific syntax (e.g., GETDATE(), DATEADD, QUALIFY in Snowflake)

• Query folding is breaking silently and you want full transparency over what gets sent to the database

Stick with the visual Power Query editor when:

• Your dataset is small or medium sized

• Team members without SQL knowledge need to maintain the queries

• The transformation logic is simple — filter, remove columns, rename

---

Native Query + SQL in Power BI

Connectors That Support Native Queries

Not every connector supports native database queries. The ones that do include the most common relational sources: SQL Server, Azure SQL Database, Azure Synapse Analytics, PostgreSQL, MySQL, Snowflake, Oracle, Google BigQuery, Amazon Redshift, IBM Db2, SAP HANA, and Azure Analysis Services (MDX or DAX).

Sources like Excel, CSV, flat text files, and most streaming or online connectors do not support native queries. If the data source does not allow custom SQL, the option will simply not appear in the connector dialog.

---

Step-by-Step: How to Use a Native Query in Power BI Desktop

Step 1 — Open Power BI Desktop and Start a New Connection

Open Power BI Desktop. On the Home ribbon, click Get Data and choose your database connector. For this guide, the example uses SQL Server database, but the approach is the same across all supported connectors.

Step 2 — Enter Server and Database Details

In the connection dialog, fill in your Server name or IP (e.g. localhost or myserver.database.windows.net) and your Database name. Do not click OK yet.

Step 3 — Expand Advanced Options and Enter Your SQL

Below the Server and Database fields, click to expand Advanced options. You will see a field labeled SQL statement. This is where you paste your native query. For example:

SELECT
    o.OrderID,
    o.OrderDate,
    c.CustomerName,
    c.Country,
    SUM(oi.Quantity * oi.UnitPrice) AS TotalOrderValue
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
WHERE o.OrderDate >= '2023-01-01'
  AND c.Country = 'Germany'
GROUP BY
    o.OrderID, o.OrderDate, c.CustomerName, c.Country

Click OK.

Step 4 — Handle the Security Prompt

Power BI will show a security warning: Permission is required to run this native database query. This is expected. Power BI cannot validate the contents of raw SQL, so it requires explicit approval before running. Since you wrote the query and know what it does, click Run.

Good to Know: This prompt appears because a native query runs with your credentials. A poorly written query could, in theory, modify or delete data if your database user has write permissions. Always review native queries before approving — especially queries written by others.

Step 5 — Review the Data Preview and Load

After approval, Power BI executes the SQL against your database and shows a preview of the results. From here you can click Load to bring the data directly into your model, or click Transform Data to open the Power Query Editor for any additional cleanup.

Step 6 — Apply and Close

If you opened the Power Query Editor, finish any additional steps and click Home → Close & Apply to load the data into the data model.

---

Using Value.NativeQuery() in M Code

There is a second way to use native queries in Power BI: the Value.NativeQuery() function in the Power Query formula bar. This approach gives you more control and — importantly — it can keep query folding active for steps that come after the native query.

This is the method recommended when you need a native SQL query as the base for further Power Query transformations, or when you need query folding to continue after the SQL step. Here is the pattern using SQL Server and the AdventureWorks sample database:

let
    Source = Sql.Database("localhost", "AdventureWorks2019"),
    NativeQuery = Value.NativeQuery(
        Source,
        "SELECT DepartmentID, Name
         FROM HumanResources.Department
         WHERE GroupName = 'Research and Development'",
        null,
        [EnableFolding = true]
    )
in
    NativeQuery

What each part does:

• Sql.Database(...) — establishes the connection at the database level. This is the correct target for native queries.

• Value.NativeQuery(Source, "...", null, [EnableFolding = true]) — sends the SQL to the data source and optionally keeps query folding active for subsequent steps.

• EnableFolding = true — tells Power BI to attempt pushing further M transformations back into SQL. This is the key difference from simply pasting SQL in the Advanced Options field.

After entering this in the formula bar, Power BI will display a warning and ask you to enable the native query for this step. Click Continue.

Pro Tip: After adding Value.NativeQuery(), apply a filter to one of your columns and right-click the new step in Applied Steps. If 'View Native Query' is still available and not grayed out, query folding is still working — your filter is being sent to the database, not processed locally.

---

Viewing the Native Query Power BI Generates Automatically

Even when you are not writing SQL yourself, you can inspect what query Power BI sends to your data source. This is extremely useful for debugging slow queries or confirming that query folding is happening.

How to view the auto-generated native query:

1. Open the Power Query Editor

2. Apply a few transformation steps (filter, remove columns, sort)

3. In the Applied Steps pane, right-click a step

4. If query folding is active, the option 'View Native Query' will be available

5. Click it to see the exact SQL Power BI is sending to the database

If that option is grayed out on a step, query folding has broken at that point or earlier. Everything from that step forward is processed locally by the Power Query engine — not by the database.

Common Mistake: Adding a custom column, a merge with a different source, or a List.Generate step typically breaks query folding. Once broken, all subsequent steps run in memory locally — even simple filters that would have been trivially fast in SQL.

---

Practical Example: Replacing a Slow Multi-Table Query

Imagine you have three tables in SQL Server: Orders, Customers, and OrderItems. You previously loaded all three separately into Power BI and joined them with relationships. Refresh is slow because all rows from all three tables are being imported.

By writing a native query that pre-joins and pre-aggregates the data at the source, you can reduce the imported dataset dramatically:

SELECT
    YEAR(o.OrderDate)   AS OrderYear,
    MONTH(o.OrderDate)  AS OrderMonth,
    c.Country,
    c.Segment,
    SUM(oi.Quantity * oi.UnitPrice) AS Revenue,
    COUNT(DISTINCT o.OrderID)       AS OrderCount
FROM Orders o
JOIN Customers c  ON o.CustomerID  = c.CustomerID
JOIN OrderItems oi ON o.OrderID   = oi.OrderID
WHERE o.OrderDate >= '2022-01-01'
GROUP BY
    YEAR(o.OrderDate), MONTH(o.OrderDate), c.Country, c.Segment

Instead of importing three raw tables totalling millions of rows, this returns a pre-aggregated summary — exactly what the dashboard needs. Refresh time drops, model size shrinks, and DAX runs faster.

---

Managing Native Query Security Settings

Power BI includes a security prompt by default for native queries that come from outside the standard connector dialogs. You can control this behaviour.

To disable the prompt globally (for trusted environments only):

1. Go to File → Options and settings → Options

2. Under Global → Security

3. Uncheck Require user approval for new native database queries

To revoke previously approved queries:

1. Go to File → Options and settings → Data source settings

2. Select Global permissions, then choose the data source

3. Click Edit permissions → Revoke Approvals

Common Mistake: Disabling the security prompt in a shared environment means any native query can run without review. Keep the prompt enabled in team settings — it only takes one click to approve a query you have already reviewed.

---

Bonus Tips and Common Mistakes

Do not use DDL commands in native queries. Operations like CREATE TABLE, DROP TABLE, or ALTER TABLE are not supported and will cause errors. Native queries in Power BI are intended to be read-only data retrieval operations.

Hardcoded date values make maintenance painful. If you write WHERE OrderDate >= '2023-01-01' directly in your SQL, you have to edit the query every time the date changes. Use Power Query parameters instead — define a parameter in Power BI Desktop, then reference it in your M code using Value.NativeQuery().

Test your SQL in the database first. Before pasting SQL into Power BI, run it in SQL Server Management Studio or Azure Data Studio. Confirm it returns the right data and runs in an acceptable time.

Native queries and DirectQuery: If your model uses DirectQuery mode, a manually written native query acts as a base subquery — additional filters from slicers and visuals are wrapped around it. This works but can complicate query folding and performance. Test thoroughly before deploying.

---

Final Thoughts

Native queries are one of the most practical tools in a Power BI developer's toolkit — and one of the most underused. Whether you write SQL in the Advanced Options field at connection time or use Value.NativeQuery() in the formula bar with EnableFolding = true, the principle is the same: give the database engine the exact instruction it needs, and let it do the heavy lifting.

The result is faster refreshes, smaller models, and cleaner logic — especially on large or complex data sources.

If you found this guide useful, explore the related posts below or download a free Power Query cheat sheet by subscribing to the Excelized newsletter.

If you found this guide useful, explore the related posts below or download a free Power Query cheat sheet by subscribing to the Excelized newsletter.