How to Use SQL in Power BI

While Power BI's built-in tools are incredibly powerful for connecting and transforming data, you can unlock a new level of control and performance by writing your own SQL queries. This guide will walk you through exactly how and when to use SQL in Power BI, allowing you to shape, filter, and aggregate your data at the source before it ever gets loaded into your report.

Why Use SQL with Power BI?

You might wonder why you'd bother writing code when Power BI’s Power Query editor has so many point-and-click options. While the visual interface is great for many tasks, directly using SQL gives you a few key advantages, especially when working with large or complex databases.

• Better Performance: Sending a pre-filtered and pre-aggregated query to your database is often much faster than pulling an entire massive table into Power BI and then filtering it. Your database is optimized for running these kinds of operations, so letting it do the heavy lifting reduces refresh times and makes your reports snappier.
• Greater Control and Precision: SQL lets you perform complex transformations that can be clunky or difficult to replicate in Power Query. You can write intricate joins across multiple tables, use advanced window functions, or perform specific calculations that you already know how to code in SQL.
• Leverage Existing Business Logic: Many companies already have battle-tested SQL views and stored procedures that contain important business logic. Instead of recreating that logic in Power BI, you can simply call the existing procedure, ensuring consistency and saving yourself a lot of development time.
• Reduce Data Load: By selecting only the columns and rows you absolutely need, you minimize the amount of data transferred and stored in your Power BI file. This is crucial for keeping your .PBIX files lean and efficient.

Connecting to a SQL Database in Power BI

Before you can write a query, you need to establish a connection to your database. Power BI has native connectors for most popular SQL-based systems like Microsoft SQL Server, PostgreSQL, MySQL, Amazon Redshift, and Snowflake. The process is very similar for all of them.

Here’s the step-by-step process using the widely used SQL Server connector as an example.

1. Open Power BI Desktop and Select 'Get Data'

From the 'Home' tab in the ribbon, click on the 'Get Data' icon. If you don’t see your desired database type in the initial dropdown, click 'More...' to open the full list of available data sources.

2. Choose Your Database Type

In the 'Get Data' window, you can either select 'Database' from the categories on the left or use the search bar. Find and select 'SQL Server database' and click 'Connect'.

3. Enter Server and Database Details

A new window will pop up asking for the connection details.

• Server: This is the name or IP address of your database server. For example, it might be something like sqlsrv01.mycompany.com or a specific IP address.
• Database (optional): You can specify the exact database you want to connect to here. If you leave this blank, Power BI will usually connect to the default database on the server, and you can pick one in the next step.

4. Choose Your Data Connectivity Mode

There is a critical choice here between Import and DirectQuery.

• Import: This is the default and most common mode. Power BI copies the data from your query into the PBIX file. Dashboards are generally very fast because the data is stored in memory. However, the data is only as fresh as your last scheduled refresh. You can write a custom SQL query in this mode.
• DirectQuery: This mode does not copy the data. Instead, Power BI sends queries directly to your database every time a user interacts with a visual. This means the data is always live. It can be slower, and there are more limitations. You cannot write a custom SQL query directly in the connection box in this mode, you'd typically query views or full tables.

For the purposes of this tutorial, we will focus on using SQL with the Import mode.

Writing a Custom SQL Query in Power BI

Let's unlock the real power. This is where you tell Power BI to stop asking for a table and instead use a query you provide.

Step 1: Expand the 'Advanced Options'

In the same SQL Server database connection window, you'll see a small dropdown arrow labeled 'Advanced options'. Click it to reveal a text box for your 'SQL statement'.

This box is where your custom query goes. Whatever you type here, Power BI will send directly to your SQL server to execute.

Step 2: Write Your SQL Statement

Now, simply write the query you need. It can be as simple or as complex as necessary. Let's start with a practical example. Imagine you have a massive SalesOrders table but only want to analyze sales from the last year for a specific product category.

Example: Filtering for Specific Data

Instead of importing the entire multi-million row table, you can pre-filter it. Type the following query into the 'SQL statement' box:

SELECT
    OrderID,
    OrderDate,
    CustomerID,
    ProductCategory,
    LineItemTotal
FROM
    Sales.SalesOrders
WHERE
    OrderDate >= '2023-01-01'
    AND ProductCategory = 'Electronics'

This query instructs the database to only return the orders from 2023 onwards that fall under the 'Electronics' category. All other data is left behind, resulting in a much smaller and faster data import.

Step 3: Preview and Load Data

After entering your query, click 'OK'. Power BI will ask for credentials if it's the first time you're connecting. Once authenticated, Power BI will run your query against the database and show you a preview of the returned data. If it looks correct, click 'Load' to bring it into your model or 'Transform Data' to fine-tune it further in the Power Query editor.

Advanced SQL Techniques for Power BI

Filtering is just the start. You can handle much more complex logic directly in your initial SQL statement. Here are a couple of very common and powerful use cases.

Joining Multiple Tables

Often, the data you need is spread across multiple tables. For instance, your sales order table might have a CustomerID, but the actual customer name is in a separate Customers table. You can use a SQL JOIN to combine them at the source.

SELECT
    so.OrderID,
    so.OrderDate,
    c.CustomerName,
    c.Region,
    so.LineItemTotal
FROM
    Sales.SalesOrders so
INNER JOIN
    Sales.Customers c ON so.CustomerID = c.CustomerID
WHERE
    so.OrderDate >= '2023-01-01'

This query pulls data from both the orders and customers tables in a single step. Doing this in SQL is usually much more efficient than importing two full tables into Power BI and then merging them in Power Query.

Aggregating Data with GROUP BY

What if you don't even need individual transaction data? Perhaps you just need a summary table of total sales by region. You can ask your SQL database to do the calculation for you using GROUP BY.

SELECT
    c.Region,
    SUM(so.LineItemTotal) as TotalSales,
    COUNT(DISTINCT so.OrderID) as NumberOfOrders
FROM
    Sales.SalesOrders so
INNER JOIN
    Sales.Customers c ON so.CustomerID = c.CustomerID
GROUP BY
    c.Region
ORDER BY
    TotalSales DESC

This query doesn't just combine tables - it calculates a summary. The result is a very small, aggregated table that's perfect for building high-level overview reports in Power BI, without needing to load millions of raw transaction rows.

Working with Your SQL Query After Import

Once your data is loaded, you’re not locked in. You can easily modify your SQL query later if your requirements change.

How to Edit an Existing SQL Query

1. Open the 'Power Query Editor' (click 'Transform data' on the Home ribbon).
2. In the 'Query Settings' pane on the right, you'll see a list of 'APPLIED STEPS'.
3. The very first step is usually named Source. Click on the gear icon ⚙️ next to it.
4. This will reopen the same database connection window from the beginning, allowing you to edit the SQL statement you entered. Click 'OK' once you've made your changes, and Power Query will refresh the data using the new query.

A Few Best Practices and Things to Watch For

• Query Folding Warning: "Query Folding" is a Power BI feature where it tries to translate steps you make in the Power Query Editor (like filtering, sorting, or removing columns) back into one single SQL query to send to the source. Writing your own native SQL query at the beginning can sometimes prevent query folding for subsequent steps, as Power BI might not understand how to combine your code with its own. Test your refresh speeds to see what gives you the best results.
• Keep It Readable: Just because the query is in a small text box doesn't mean it should be sloppy. Format your SQL with line breaks and indentation. You can write your query in a proper SQL editor first, then copy and paste it into the Power BI window. Your future self will thank you.
• Don't Be Afraid of Views: If your query is becoming extremely long and complex, consider creating a VIEW in the database itself. A view saves your query on the server and allows you to SELECT * from it in Power BI like it’s a simple table, keeping your Power BI connection clean.

Final Thoughts

Learning to inject custom SQL into your Power BI connections is a true force multiplier. It gives you precise control over your data retrieval, letting you tap into the full analytical power of your database to filter, join, and aggregate information before it even lands in your report, leading to faster and more efficient dashboards.

While mastering SQL in Power BI is a great way to streamline your workflow, it introduces another layer of code and complexity to manage. We designed Graphed to remove this friction altogether. Instead of manually writing and debugging SQL, you can just connect your data sources and describe the dashboard or report you want in plain English. We instantly build live, interactive dashboards that answer your questions in seconds, giving you all the power without the steep learning curve.