Where to Write SQL Query in Power BI?

Want to pull hyper-specific data into your analysis by writing an SQL query directly in Power BI? It’s a powerful way to streamline your reports by grabbing exactly what you need from the source. This guide will show you the two primary places where you can run SQL commands in Power BI, complete with step-by-step instructions for each method.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Write Your Own SQL in Power BI?

While Power BI's standard visual interface for getting data is excellent, sometimes you need more control and precision. Writing your own Structured Query Language (SQL) query gives you several key advantages:

• Precision: Instead of importing entire tables and filtering them later in Power Query, you can write a SELECT statement to retrieve only the specific columns and rows you need from the start.
• Improved Performance: Asking the source database (like SQL Server) to perform complex logic, joins, or aggregations with your query is often much faster. The database is optimized for this work, and it reduces the amount of data transferred and processed by Power BI. This concept is often called "query folding."
• Handling Complexity: You can perform complex transformations, CASE statements, or data type conversions in your SQL query that might be more cumbersome to replicate using the Power Query interface.
• Access Views and Stored Procedures: It allows you to directly call existing views or execute stored procedures on your database, leveraging pre-built logic managed by your data team.

Method 1: Writing SQL When You First Connect to a Database

The most common and straightforward place to write an SQL query is within the dialogue box that appears when you first connect to a direct query data source, like a SQL Server, PostgreSQL, or MySQL database. This method essentially defines your entire data source using a single, static SQL script.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step-by-Step Guide to Using the SQL Statement Option

Let's walk through the process using a SQL Server database as an example. The steps are very similar for other SQL-based connectors.

1. Get Data from a SQL Source: In Power BI Desktop, navigate to the Home tab on the ribbon and click Get Data. From the dropdown menu, select SQL Server.

2. Enter Server and Database Info: A dialogue box will appear asking for the Server and (optionally) the Database name. Fill these fields out with the details for the database you want to connect to.

3. Open the Advanced Options: This is the critical step. Before clicking "OK," click on the Advanced options dropdown. This will reveal several additional settings, including a text box labeled SQL statement.

4. Enter Your SQL Query: This box is where you write or paste your custom SQL query. For instance, imagine you have a large Sales table but only need sales from the current year and a few specific columns. Your query might look something like this:

SELECT
    TransactionID,
    SaleDate,
    CustomerID,
    ProductCategory,
    Quantity,
    UnitPrice
FROM
    dbo.Sales
WHERE
    YEAR(SaleDate) = 2024,

5. Load Your Data: After pasting your script, click OK. Power BI will then authenticate with the database (you may need to provide credentials) and execute your query. A preview of the results will be shown, and you can then click Load or Transform Data to bring the output directly into Power BI.

Now, your Power BI table will only contain the precise data you requested in your query. Any further transformations in Power Query will be applied after this initial SQL pull.

Important Considerations for This Method

• It's Static: The SQL query entered here is fixed. If you need to change date ranges or add a column, you have to go back into the data source settings to edit the original script. To do this, go to Transform data > Query Settings pane > click the gear icon next to the Source step.
• Limited Dynamic Filtering: This method does not easily support dynamic parameters from Power BI reports. The query runs "as is" and doesn't know about any slicers or filters active on your report canvas.
• Query Folding: For many data sources, Power BI will try to "fold" subsequent transformation steps from the Power Query editor into this native SQL query, promoting efficiency. However, writing a very complex initial script can sometimes hinder this process.

Method 2: Using a Native Database Query in Power Query

For more flexibility and dynamic control, you can use the Power Query Editor to run SQL. This advanced method lets you construct and execute queries using M language functions, which means you can even build the SQL text dynamically using parameters.

When to Use This More Advanced Method?

This approach shines when you need to change the query based on user input or other conditions. Imagine you've created a parameter in Power BI for a user to select a specific Region. You can use this method to inject that user's selection directly into your SQL WHERE clause, making the data model more interactive and efficient.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Run a Native Query in Power Query

This process feels a little less direct, but it unlocks significant power. We'll use the Value.NativeQuery() function.

1. Establish a Basic Connection: First, connect to your database (e.g., Get Data > SQL Server) but without using the advanced options. Just enter the server and database name and click connect. In the Navigator window, select any small table just to establish the connection.

2. Open the Advanced Editor: Click Transform Data to open the Power Query Editor. With the new query selected, go to the View tab and click on Advanced Editor. This will show the M code behind your connection.

3. Replace the Code with a Native Query: You are going to replace the code that selects the table with your custom SQL function. The code looks like this:

let
    Source = Sql.Database("your-server-name", "your-database-name"),
    RunMyQuery = Value.NativeQuery(
        Source, 
        "SELECT Product, Region, SUM(SalesAmount) AS TotalSales FROM dbo.Sales WHERE Region = 'North' GROUP BY Product, Region",
        null, 
        [EnableFolding=true]
    )
in
    RunMyQuery

Let's break that down:

• Source: Establishes the connection to the database.
• RunMyQuery = Value.NativeQuery(...): Executes your custom SQL.
• First parameter: the database connection (Source).
• Second parameter: your SQL query as a text string.
• Third parameter: null (for parameters).
• Fourth parameter: optional options, e.g., [EnableFolding=true].

After pasting this in, click Done. The Power Query preview will now show the results of your native SQL query.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Bonus Round: Making Your SQL Truly Dynamic

The real power of Value.NativeQuery is making the SQL string dynamic. Let's say you have a Power Query parameter called SelectedRegion (created via Manage Parameters).

You can modify your M code to build the SQL string with this parameter:

let
    // Assuming you have a parameter named 'SelectedRegion' with a text value like 'North'
    MyRegion = SelectedRegion, 

    // Build the dynamic SQL query text string
    DynamicSQL = "
        SELECT 
            Product,
            Region, 
            SUM(SalesAmount) AS TotalSales 
        FROM 
            dbo.Sales 
        WHERE 
            Region = '" & MyRegion & "'
        GROUP BY 
            Product, Region
    ",

    Source = Sql.Database("your-server-name", "your-database-name"),
    RunMyQuery = Value.NativeQuery(
        Source,
        DynamicSQL,
        null,
        [EnableFolding=true]
    )
in
    RunMyQuery

In this example, the M code takes the value from the SelectedRegion parameter and concatenates it directly into the SQL string. Now, if you change the parameter's value in Power Query and refresh, an entirely new query is sent to the database. Note: Be cautious with this method for text values, as it can be vulnerable to SQL injection if an input source is not controlled.

Comparing Methods: Which One Is for You?

So, which approach should you use when you want to run SQL in Power BI?

Go with Method 1 (SQL Statement in Get Data) when:

• Your query is well-defined and unlikely to change often.
• You want the simplest, most direct experience.
• You're simply aiming to pull a specific subset of data for later transformation in Power Query.

Go with Method 2 (Native Query in Power Query) when:

• You need to build a dynamic SQL statement that responds to parameters or other query logic.
• You are comfortable working with M code in the Advanced Editor.
• You need greater control over query execution within the Power Query workflow.

Final Thoughts

Writing your own SQL is an incredibly effective way to optimize your Power BI reports for precision and performance. Whether you're using the straightforward SQL statement box during the initial connection or tapping into the dynamic Value.NativeQuery() function, you now have the tools to query your data exactly how you need to.

This process streamlines your reporting, but juggling SQL syntax and M language still involves a learning curve. In our own work building Graphed, we focused on eliminating that step entirely. Instead of writing SQL or M code, you can use plain English like "Show me total sales by product in the North region" and Graphed connects to your live data to build real-time dashboards for you, turning hours of data work into a 30-second conversation.