How to Enter SQL in Power BI Advanced Editor

Writing your own SQL query in Power BI lets you sidestep the graphical user interface to pull the exact data you need, precisely how you need it. By using a native SQL query, you can leverage the full power of your database to perform complex joins or transformations before the data even lands in Power BI. This guide will walk you through exactly how to input SQL statements in Power BI, giving you more control and flexibility over your data connections.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Bother Using SQL in Power BI?

You might wonder why you’d need to write SQL when Power BI’s Power Query Editor is already so capable. The graphical interface is excellent for most tasks like filtering, sorting, and merging datasets. However, there are a few key scenarios where writing your own SQL provides a significant advantage:

• Improved Performance: For large databases, asking the database server to do the heavy lifting (like filtering millions of rows or executing complex joins) is often much more efficient. This process, known as "query folding," pushes the data transformation logic back to the source, reducing the amount of data transferred and processed by Power BI.
• Complex Logic: Some business logic is just easier to express in SQL. If you have custom functions, window functions (like ROW_NUMBER() or LAG()), or intricate subqueries, it’s often simpler to write them in a native query than to recreate them using Power Query’s M language.
• Leveraging Existing Code: If you or your data team already have optimized SQL queries and stored procedures, there's no need to reinvent the wheel. You can plug those queries directly into Power BI to ensure consistency and save a ton of time.
• Accessing Specific Data: Sometimes, you need data from tables, views, or database functions that aren't easily selectable in the standard Power BI navigator. A custom query gives you direct access to anything your user permissions allow.

Getting Started: Two Methods for Entering SQL Queries

There are two primary ways to run a SQL query in Power BI. The first method is done when you initially connect to the data source and is the most straightforward for new queries. The second involves modifying an existing connection using the Advanced Editor in Power Query.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 1: Entering a SQL Statement at Connection (The Easy Way)

This is the simplest and recommended method when you are starting a new report or adding a new data source that requires a custom query.

Step 1: Get Data

In Power BI Desktop, navigate to the Home tab on the ribbon and click on Get Data. From the dropdown, select the database source you want to connect to. For this example, we’ll use SQL Server database, but the process is very similar for other supported databases like PostgreSQL, MySQL, and Oracle.

Step 2: Connect to the Database

The SQL Server database connection window will appear. Fill in your Server name and, if necessary, the Database name. You can often leave the database name blank if your query specifies it.

Here’s the most important part: click to expand the Advanced options section at the bottom of the window.

Step 3: Paste Your SQL Query

Expanding the advanced options reveals several new settings, including a text box labeled SQL statement. This is where you enter your native query. You can write or paste any valid SQL query for your database here.

For example, let’s say we want to pull a filtered list of high-value orders from the last year from a Sales.Orders table. You could paste a query like this:

SELECT 
    OrderID,
    OrderDate,
    CustomerID,
    TotalAmount
FROM 
    Sales.Orders
WHERE
    OrderDate >= '2023-01-01'
    AND TotalAmount > 1000
ORDER BY 
    TotalAmount DESC

Step 4: Load and Transform Your Data

After pasting your query, click OK. Power BI will execute the query against your database, and a preview of the resulting data will appear in the Navigator window. You can now choose to either Load the data directly into your data model or click Transform Data to open the Power Query Editor for further adjustments.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 2: Using the Power Query Advanced Editor

What if you’ve already connected to a database by selecting a table through the Navigator and now you want to change it to a custom SQL query? Or maybe you just want to see and edit the underlying code. This is where the Advanced Editor comes into play.

Step 1: Open the Power Query Editor

First, get to the Power Query Editor by clicking Transform Data on the Home ribbon.

Step 2: Select the Source Step

In the Power Query Editor, find the query you want to edit in the Queries pane on the left. Once selected, look at the Applied Steps pane on the right. The very first step is usually named Source. Click on it to select it.

Step 3: Open the Advanced Editor

With the Source step selected, go to the Home tab in the Power Query ribbon and click on Advanced Editor. This will open a new window showing the M code that defines a specific step in your data transformation process.

If you initially selected a table from the navigator, your M code for the Source step might look something like this:

let
    Source = Sql.Database("your-server-name", "your-database-name"),
    dbo_SalesOrders = Source{[Schema="dbo",Item="SalesOrders"]}[Data]
in
    dbo_SalesOrders

This code connects to the database, then navigates to the SalesOrders table.

Step 4: Modify the M Code to Include Your SQL Query

To replace this navigation with a custom SQL query, you need to modify the Sql.Database function. This function accepts a third argument, which is a record that can contain a Query option. You will restructure the code to pass your SQL query as text.

Let's convert the setup above to use a custom query. We’ll remove the second step (dbo_SalesOrders) and add the query directly to the Source step:

let
    Source = Sql.Database("your-server-name", "your-database-name", 
        [Query="SELECT OrderID, OrderDate, CustomerID, TotalAmount FROM Sales.Orders WHERE OrderDate >= '2023-01-01' AND TotalAmount > 1000 ORDER BY TotalAmount DESC"])
in
    Source

Click Done. Power Query will now use your native SQL query instead of just pulling the entire table.

Best Practices and Important Considerations

Graphed

Still Building Reports Manually?

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

Watch the Demo

Understanding Query Folding

When you use the Power Query graphical interface for transformations (like filtering, sorting, or grouping), Power BI attempts to "fold" these steps. This means it translates your actions into a single, efficient SQL query that it sends to the source database. For example, if you filter rows, Power BI adds a WHERE clause to its SQL query.

However, when you write your own native SQL query, query folding for subsequent steps in Power Query might break. Power BI often cannot add to a handwritten SQL statement and will warn you with a message like, "This step results in a query that is not supported in the data source ('[View native query]' is disabled)."

This isn't necessarily a bad thing! It just means that any transformations you add after the custom SQL step will be processed by Power BI's engine on your local machine, not the database. Therefore, a good rule of thumb is to put as much of the filtering and transformation logic as possible inside your initial SQL query for the best performance.

Do's and Don'ts for Writing SQL in Power BI

• Do select only the columns you need. Avoid using SELECT * on wide tables. The fewer columns you bring into Power BI, the smaller and faster your report will be.
• Do pre-filter your data using a WHERE clause. Bringing in a billion rows when you only need one million is a huge performance killer.
• Don't include an ORDER BY clause unless necessary for the logic. Power BI is a data modeling tool, not a display tool for raw queries. The visual layer will handle sorting, so an ORDER BY in the initial query just creates unnecessary work for the database. A rare exception is when you need it for a ranking function within the SQL query itself.
• Do consider creating a view in your database. If your query is exceptionally complex or will be reused, creating a SQL View encapsulates the logic on the database side. Then, in Power BI, you can simply select from the view as if it were a table, which is much cleaner and easier to manage.
• Don't use it for simple tasks. If all you need to do is filter a single column or remove duplicates, the Power Query graphical interface can do that just fine and will ensure query folding continues to work. Save native SQL for the truly complex stuff.

Final Thoughts

Mastering the ability to insert custom SQL queries in Power BI is a significant step toward becoming a power user. It unlocks a new level of control, allowing you to fine-tune your data retrieval, boost performance, and tackle complex data shaping challenges right at the source, long before the data even enters your report.

While mastering the advanced features of tools like Power BI is incredibly valuable, it often involves a steep learning curve with technical details like M code and query folding. We built Graphed to simplify this entire process. Instead of writing SQL or navigating complex interfaces, you can just describe the data and charts you need in plain English. Our AI-powered analyst connects to your sources and builds interactive, real-time dashboards for you in seconds, letting your whole team access insights without needing to become data experts.