How to Enable Native Query in Power BI

Writing a native SQL query directly within Power BI can be a total game-changer for your report's performance, especially when you're wrestling with massive databases. By telling the database exactly what you need upfront, you can dramatically speed up your data refresh times. This article shows you how to enable and use native queries in Power BI, why this technique is so effective, and a few best practices to keep in mind.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is a Native Query in Power BI?

Normally, when you connect to a data source like a SQL server, Power BI's Power Query Editor lets you visually point, click, and transform your data. You might connect to a few tables, merge them, filter out rows, and remove columns. Behind the scenes, Power BI translates these clicks into its own formula language called M. In many cases, it cleverly translates these M steps into the source's native language (like SQL) through a process called query folding.

However, you can also take direct control and write your own SQL query from the start. A native query is a command written in the language of your data source (like SQL for a SQL Server database) that you instruct Power BI to run directly against that source. Instead of pulling an entire 50-million-row table into Power BI and then filtering it, you can send a SQL statement that tells the server, "Hey, just give me the 10,000 rows I actually need."

This approach pushes all the heavy lifting - the filtering, aggregating, joining, and sorting - onto the database server, which is built and optimized for exactly this kind of work. The result? Power BI receives a much smaller, pre-processed dataset, reducing load times, network traffic, and local processing power.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

When Should You Use a Native Query?

While the standard Power Query interface is excellent for most tasks, there are specific situations where writing a native query offers a significant advantage. It's not about replacing Power Query, but about knowing when to use this powerful tool in your toolbelt.

• Massive Datasets: This is the number one reason. If your source table has tens of millions (or billions) of rows, trying to import it all into Power Query for filtering is incredibly inefficient. A native query that applies a WHERE clause or aggregates data with GROUP BY on the server-side first will make your report exponentially faster.
• Executing Stored Procedures: Your database may contain pre-built stored procedures that encapsulate complex business logic. You can call these procedures directly with a native query, which is often simpler than trying to replicate that logic in Power BI.
• Accessing Advanced Database Features: Databases have a rich set of functions that may not have a simple one-to-one equivalent in Power Query. A native query allows you to leverage powerful window functions, JSON functions, or proprietary performance features that are specific to your database system.
• Complex Joins and Logic: While Power Query's merge and append features are great, sometimes it's just faster and more straightforward to write a complex multi-table JOIN with specific conditions directly in SQL, especially for developers who already "think" in SQL.

Of course, for smaller datasets or simpler transformations, sticking to the visual Power Query editor is perfectly fine. The UI is more accessible for team members who don’t know SQL and provides a clear, step-by-step audit trail of your transformations.

Step-by-Step: How to Run a Native SQL Query in Power BI

Activating the native query option is simple, but it's tucked away in an advanced menu that many users overlook. Here’s how to find it and put it to work.

For this example, let's assume we're connecting to a SQL Server database containing tables for Orders, Customers, Products, and OrderItems.

Step 1: Get Data

From the Home ribbon in Power BI Desktop, click on Get Data and select your desired source. We'll choose SQL Server database.

Step 2: Enter Server Details and Find "Advanced options"

In the dialog box, enter your server and (optionally) your database name. Don’t click "OK" just yet! This is the most crucial step. Click to expand the Advanced options section below.

Step 3: Write Your SQL Query

You’ll now see a text box labeled SQL statement (optional). This is where you’ll put your native query. By inserting a query here, you are telling Power BI PRECISELY what data to request. You are overriding the default behavior of simply showing you all the tables and views.

Let's write a query that pulls order details but only for sales in the USA since the start of 2023.

SELECT
    o.OrderID,
    o.OrderDate,
    c.CustomerName,
    p.ProductName,
    oi.Quantity,
    oi.UnitPrice
FROM
    Orders o
JOIN
    Customers c ON o.CustomerID = c.CustomerID
JOIN
    OrderItems oi ON o.OrderID = oi.OrderID
JOIN
    Products p ON oi.ProductID = p.ProductID
WHERE
    o.OrderDate >= '2023-01-01'
    AND c.Country = 'USA'

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Approve the Native Query Security Warning

When you click OK, Power BI will almost certainly show you a security permission prompt that says, "Approval is required to run a native database query."

Don't be alarmed. Power BI shows this because it can't validate the content of your SQL code. It's a safeguarding measure to ensure you intentionally want to run this command, as a poorly written or malicious query could potentially modify or delete data (if permissions allow). Since you wrote the query, click Run.

Step 5: Load or Transform the Pre-filtered Data

After you approve the query, Power BI will execute it and show you a preview of the resulting data - in our case, only the USA-based order details from 2023. From here, you can click Transform Data to open the Power Query Editor for any additional light-touch cleanup or refinement, or just click Load to bring it straight into your data model.

Going Further: Using Parameters in Native Queries

Hardcoding values like '2023-01-01' or 'USA' in your script works, but it isn't flexible. If you want to change the date or country, you have to dig back into the source query and edit the code. A far better approach is to use Power Query parameters to make your native query dynamic.

1. Create Your Parameters

In the Power Query Editor, go to the Home ribbon and click Manage Parameters > New Parameter. Let's create two:

• Name: CountryName
• Type: Text
• Current Value: USA

And a second one for the date:

• Name: StartDate
• Type: Date
• Current Value: 1/1/2023

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. Modify the Source M Code

Select your query in the left pane, then click the gear icon for the Source step. This just opens the same initial dialog box. It's easier to edit the code directly. Click on the Advanced Editor in the Home ribbon to see the underlying M code.

You'll see something like this:

let
    Source = Sql.Database("your_server", "your_database", [Query="SELECT...WHERE...AND c.Country = 'USA'"])
in
    Source

We need to edit the query string to incorporate our parameters. This requires a little M language text concatenation.

let
    Source = Sql.Database("your_server", "your_database", [Query="
        SELECT
            o.OrderID,
            o.OrderDate,
            c.CustomerName
        FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID
        WHERE
            o.OrderDate >= '" & Date.ToText(StartDate, "yyyy-MM-dd") & "'
            AND c.Country = '" & CountryName & "'
    "])
in
    Source

Syntax Breakdown:

• We break the string with double quotes ".
• The ampersand & is the concatenation operator in M.
• Date.ToText(StartDate, "yyyy-MM-dd") converts our date parameter into a text string in a format that SQL understands.
• Notice the single quotes ' we add around the parameter values - these are necessary for the final SQL syntax.

Now, whenever you change the values in the CountryName or StartDate parameters in Power Query, they will be automatically inserted into the native SQL query before it's sent to the server. This gives you the performance of a native query and the flexibility of parameterized reporting.

Best Practices and Potential Risks

Native queries are incredibly useful, but it pays to be mindful of a few things:

1. Security First: Always follow the principle of least privilege. The account Power BI uses to connect to the database should ideally have read-only permissions (db_datareader role in SQL Server). This prevents any risk of someone injecting an UPDATE or DELETE statement in the native query box.
2. Maintainability: A complex SQL query is less transparent than a series of named steps in the Power Query UI. If you'll be handing the report over to others, make sure your queries are well-commented and easy to understand.
3. Don’t Overdo It: The best approach is often a hybrid one. Use the native query to handle the initial, most demanding work - like filtering down a multi-billion row fact table. Then, use the standard Power Query interface for subsequent steps like transformations, adding conditional columns, or unpivoting. This gives you the best of both worlds: performance and usability.

Final Thoughts

Enabling a native query in Power BI allows you to take direct control over data retrieval, pushing the heavy processing back to the database server where it belongs. It's an indispensable technique for building fast, efficient reports on top of very large datasets and is a great trick to have up your sleeve.

While mastering M code and SQL is a great skill for handling large-scale data, that requirement can be a hurdle when you just need answers. We wanted to eliminate that friction entirely. With Graphed , you connect your data sources in a few clicks, and instead of writing code, you just describe the report you need in plain English. We handle connecting to your platform and translating your question into the right query in the background, delivering a live dashboard in seconds.