Connecting to SQL Server and Relational Databases in Power Query: Writing Native Queries and Managing Credentials

Introduction

Imagine you're a data analyst at a mid-sized manufacturing company. Your company's production data — orders, inventory, shipments, defect logs — all lives in a SQL Server database. Every week, you manually export CSVs, clean them up in Excel, and build reports. It takes hours. Meanwhile, someone in IT keeps reminding you that "all that data is already in the database, you just need to connect to it." They're right, and today we're going to do exactly that.

Power Query has a first-class connector for SQL Server and most other relational databases. But connecting to a database is fundamentally different from opening a CSV file. A file sits passively on your hard drive. A database is a live system with security rules, user permissions, and thousands — sometimes billions — of rows. That means you need to handle credentials properly, and you need to understand how to ask the database only for exactly what you need, rather than pulling the entire thing into your computer's memory.

By the end of this lesson, you'll know how to establish a connection to SQL Server from Power Query, navigate the available tables, write a native SQL query to pull exactly the data you want, and manage credentials so your connections are secure and maintainable. These skills transfer almost directly to other relational databases like PostgreSQL, MySQL, and Azure SQL Database.

What you'll learn:

• How to connect to a SQL Server database from Power Query in Excel or Power BI
• The difference between importing a full table versus writing a native SQL query
• How to write native queries that filter, join, and shape data at the database level
• How to set up, update, and troubleshoot database credentials
• How query folding relates to database connections and why it matters

---

Prerequisites

• Basic familiarity with Power Query's interface (you've opened the Power Query Editor before)
• A basic understanding of what a SQL SELECT statement looks like (we'll cover this as we go, but knowing SELECT * FROM TableName is helpful)
• Access to either Power BI Desktop (free) or Excel with the Power Query add-in (Excel 2016 or later includes it natively)
• Ideally, access to a SQL Server instance — if you don't have one, we'll note where you can get a free practice database

---

What Makes a Database Connection Different from a File Connection

When you connect to a CSV or Excel file, Power Query reads the entire file and loads it into a temporary workspace. The file doesn't care who reads it or how often. A relational database is a different creature entirely.

A relational database is a structured system that stores data in tables, enforces relationships between those tables, and controls who can access what through a system of logins and permissions. SQL Server is Microsoft's enterprise relational database — the same engine that powers everything from small business applications to Fortune 500 ERP systems.

When Power Query connects to SQL Server, it's not reading a file. It's opening a network connection, authenticating with the server, sending a query (a request for data written in SQL), and receiving the results. This means several things matter that didn't matter with files:

1. Authentication — the database needs to know who you are before it gives you anything
2. Query efficiency — asking for the right data upfront is far better than pulling everything and filtering later
3. Network and server permissions — you can only see what your user account has been granted access to

Understanding these differences will help you avoid the most common pitfalls, especially the one where a colleague complains that "Power Query is so slow" because they loaded a 50-million-row table when they only needed last month's records.

---

Setting Up Your Connection: Connecting to SQL Server

Let's walk through the connection process from scratch.

In Power BI Desktop

Open Power BI Desktop and click Get Data in the Home ribbon. A dialog box will appear with a search bar and a categorized list of data sources. Type "SQL" in the search bar, select SQL Server, and click Connect.

A connection dialog appears with two fields: Server and Database.

• Server: This is the network address or machine name of your SQL Server. It might look like PROD-SQL-01, 192.168.1.50, or a fully qualified domain name like sqlserver.yourcompany.com. If your SQL Server is running on a named instance (a way of hosting multiple SQL Server installations on one machine), the format is ServerName\InstanceName, for example PROD-SQL-01\REPORTING.
• Database: This is optional at this stage. SQL Server can host dozens of individual databases — you can either specify one now or browse all of them after connecting. For clarity, it's best to specify the database name here if you know it. For our manufacturing scenario, let's say the database is named ProductionDB.

Leave the Data Connectivity mode set to Import for now (we'll discuss DirectQuery another time), and click OK.

In Excel

In Excel, click the Data tab in the ribbon. In the Get & Transform Data group, click Get Data → From Database → From SQL Server Database. You'll see the same Server and Database fields.

The Navigator: Browsing Your Database

After clicking OK, Power Query will prompt you for credentials (we'll cover that in detail shortly). Once authenticated, you'll see the Navigator panel — a tree view of everything your user account can access in that database.

In the Navigator, you'll typically see folders for Tables, Views, and sometimes Stored Procedures and Functions. Expanding a table shows you a preview of its data on the right side.

For our ProductionDB scenario, imagine you can see tables like:

• Orders
• OrderLineItems
• Products
• Customers
• ShipmentLog
• DefectReports

You could click Orders and hit Load to pull the whole table in. But if Orders has five years of data and you only need the last 90 days, loading the entire thing is wasteful and slow. This is where native queries become your best friend.

---

Writing Native Queries: Letting the Database Do the Heavy Lifting

A native query is SQL code that you write and send directly to the database, instead of letting Power Query import a table and then filter it afterward. The database executes your SQL, and only the resulting rows travel across the network to Power Query. This is dramatically more efficient.

How to Use a Native Query in the Connection Dialog

Go back to the Get Data → SQL Server Database dialog. Enter your Server and Database as before. Before clicking OK, expand the Advanced options section at the bottom of the dialog.

You'll see a text box labeled SQL statement (optional, requires database). This is where you write your native query. Note: the Database field becomes required when you use this option.

Let's write our first native query. For the manufacturing scenario, we want orders from the last 90 days:

SELECT
    o.OrderID,
    o.OrderDate,
    o.CustomerID,
    c.CustomerName,
    o.TotalAmount,
    o.Status
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= DATEADD(day, -90, GETDATE())
  AND o.Status <> 'Cancelled'
ORDER BY o.OrderDate DESC

This query does several things that would otherwise require multiple Power Query transformation steps:

• It joins two tables (Orders and Customers)
• It filters to only the last 90 days using DATEADD, a SQL Server function
• It excludes cancelled orders
• It orders results by date

All of this happens on the SQL Server before a single byte comes to Power Query. Enter this query and click OK.

Important: When you use a native SQL query, Power Query cannot modify or optimize the SQL you've written. It will send exactly what you type to the server. This means you're responsible for writing correct, safe SQL. Always test your query in a SQL tool (like SQL Server Management Studio or Azure Data Studio) before pasting it into Power Query.

What If You Don't Know SQL Well?

There's a practical middle path: use the Navigator to load a table, build your transformations in the Power Query Editor using the graphical interface, and then let Power Query's query folding mechanism handle pushing filters back to the database. We'll cover this next.

---

Understanding Query Folding

Query folding is one of the most important concepts for database connections in Power Query, and it's one of the least understood.

When you import a table and then apply filters, Power Query tries to translate your transformation steps back into SQL and send them to the database as part of the original query. This translation process is called query folding. When it succeeds, your filter runs on the server. When it fails, Power Query pulls all the data first and filters it locally.

Here's how to check if your steps are folding: In the Power Query Editor, right-click on a step in the Applied Steps panel on the right side. If you see the option View Native Query, that step is folding — Power Query is generating SQL behind the scenes. If that option is grayed out, the step is being processed locally.

When Query Folding Breaks

Some transformations can't be translated back to SQL. Common culprits include:

• Adding a custom column using Power Query's M language functions that have no SQL equivalent
• Using certain text transformation functions
• Merging with a query that comes from a different data source (like combining a database table with a CSV file)

Once any step breaks query folding, all subsequent steps also run locally. This is why the order of your steps matters. Put filters and column selections early (these fold well) and complex transformations later.

Pro tip: After connecting to a SQL Server table, immediately apply your row filters (date ranges, status fields, etc.) and select only the columns you need using Choose Columns. These steps typically fold, reducing the data volume before any local processing begins.

---

Managing Credentials: Security Without the Headaches

This is where many beginners get tripped up. Power Query needs to authenticate with SQL Server every time it refreshes data, so it stores credentials. Understanding how this works will save you hours of troubleshooting.

The Two Authentication Methods

When Power Query prompts you for credentials after connecting to SQL Server, you'll see a dialog with several options. The two main ones are:

1. Windows Authentication This uses your current Windows login to authenticate with the database. If your organization has set up SQL Server to trust Windows user accounts (this is called "Windows Authentication" or "integrated security"), your credentials are passed automatically. You don't type a username or password — SQL Server trusts that if Windows says you're DOMAIN\jsmith, then you are.

This is the preferred method in enterprise environments because:

• No passwords stored in Power Query files
• Credentials rotate when your Windows password changes
• IT can manage database access centrally through Active Directory groups

To use it, in the credential dialog, select Windows and leave the credential fields blank (or pre-filled with your Windows credentials).

2. Database Authentication (SQL Server Authentication) This uses a username and password created specifically in SQL Server, independent of Windows. You'll see this in cloud databases, external vendor systems, or older SQL Server setups.

In the credential dialog, select Database and enter the SQL Server login name and password. For example, a service account might be powerquery_reader with a specific password.

Warning: Be careful about where these files live. If you use SQL authentication and someone else opens your Power BI file or Excel workbook, they'll be prompted for credentials — but if the file contains sensitive data and is shared via a network share without proper controls, that's a risk. Always think about who can access the file, not just the database.

Where Power Query Stores Credentials

Power Query stores credentials per data source, not per query or file. This is a crucial distinction. The credential is tied to the combination of server address and database.

In Power BI Desktop, go to File → Options and settings → Data source settings. You'll see a list of every data source you've ever connected to. You can edit credentials, delete them, or change the privacy level here.

In Excel, go to Data → Queries & Connections, then in the Power Query Editor, go to File → Options and settings → Data source settings — same interface.

Changing or Updating Credentials

If your database password changes (or if you initially entered the wrong credentials), you'll need to update them. The error you'll see looks something like: "We couldn't connect to your data. Details: Microsoft SQL: Login failed for user 'jsmith'."

To fix it:

1. Open Data source settings as described above
2. Find the SQL Server source
3. Click Edit Permissions or Edit Credentials
4. Update the username and password
5. Click Save and try refreshing again

Tip: If you're setting up a Power BI report that will be published to the Power BI Service and refreshed on a schedule, you'll need to reconfigure credentials in the Power BI Service as well, through a Data Gateway. The credentials stored in your Desktop file don't automatically transfer to the cloud.

Privacy Levels

When you connect to multiple data sources — say, a SQL Server database and an Excel file — Power Query asks you to set Privacy Levels. These tell Power Query whether data from one source can be combined with data from another.

• Private: No data from this source can be shared with other sources
• Organizational: Data can be shared with sources at the same privacy level
• Public: Data can be shared with any source

For most internal corporate data scenarios, set both sources to Organizational. Setting one source to Private will prevent query folding across sources and usually cause frustrating performance issues without any actual security benefit in a controlled environment.

---

Hands-On Exercise

For this exercise, you'll build a practical data connection using what we've covered. If you have access to a SQL Server, use it. If not, download the AdventureWorks sample database — it's a free Microsoft sample database that you can restore to a local SQL Server Express instance (also free) or access through several publicly available cloud instances.

Scenario: You're building a weekly sales summary report. You need orders from the AdventureWorks database, specifically from the Sales.SalesOrderHeader and Sales.Customer tables, filtered to the year 2013.

Step 1: Connect Open Power BI Desktop. Click Get Data → SQL Server Database. Enter your server name and AdventureWorks2019 as the database. Expand Advanced options.

Step 2: Write the native query In the SQL statement box, enter:

SELECT
    soh.SalesOrderID,
    soh.OrderDate,
    soh.TotalDue,
    soh.Status,
    soh.OnlineOrderFlag,
    c.CustomerID,
    soh.ShipToAddressID
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.Customer c ON soh.CustomerID = c.CustomerID
WHERE YEAR(soh.OrderDate) = 2013
ORDER BY soh.OrderDate

Click OK and authenticate with Windows Authentication when prompted.

Step 3: Verify in the Editor Click Transform Data to open the Power Query Editor. You should see roughly 3,915 rows (the 2013 orders in AdventureWorks). Notice the column types — check that OrderDate was recognized as a Date/Time type and TotalDue as a decimal number. If not, right-click the column header and set the type manually.

Step 4: Check credential storage Close and save the Power BI file. Reopen it. Does it refresh without prompting you for a password? If you used Windows Authentication, it should. Go to File → Options and settings → Data source settings to confirm your credential is saved.

Step 5: Modify the query Now imagine requirements change and you also need orders from 2014. In the Power Query Editor, right-click your query and select Advanced Editor. You'll see the M code that Power Query generated. Find the native query text inside Value.NativeQuery() and change = 2013 to >= 2013. Click Done and verify the row count increases.

---

Common Mistakes & Troubleshooting

"I can see the server but not the database I need" Your user account may not have been granted access to that specific database. Contact your database administrator and ask them to grant db_datareader role access to your Windows login for the required database.

"My native query runs fine in SSMS but fails in Power Query" Two common reasons: First, Power Query doesn't support multi-statement SQL (multiple statements separated by semicolons). Your query must be a single SELECT statement. Second, some SQL Server features like temporary tables (#TempTable) aren't supported in native queries. Rewrite using CTEs (Common Table Expressions) instead:

WITH RecentOrders AS (
    SELECT * FROM Orders WHERE OrderDate >= DATEADD(day, -90, GETDATE())
)
SELECT ro.*, c.CustomerName
FROM RecentOrders ro
JOIN Customers c ON ro.CustomerID = c.CustomerID

"Refresh works on my machine but fails when published to Power BI Service" The Power BI Service can't reach your on-premises SQL Server directly. You need to install and configure an On-premises Data Gateway — a piece of software that sits on your network and acts as a secure bridge between the Power BI cloud service and your internal database.

"I keep getting prompted for credentials even after saving them" This usually happens when the server address in the connection doesn't exactly match the stored credential. For example, if you initially connected to PROD-SQL-01 and now the query uses PROD-SQL-01.yourcompany.com, Power Query treats these as different sources. Standardize on one format — fully qualified domain names are most reliable.

"My date filter in the native query doesn't update automatically" If you hardcoded a date like WHERE OrderDate >= '2024-01-01', that won't change on its own. Use relative date functions like DATEADD(day, -90, GETDATE()) in SQL, or use Power Query parameters to pass dynamic values into your native query — a technique worth exploring as you advance.

---

Summary & Next Steps

You now have the foundational skills to connect Power Query to SQL Server like a professional. Here's what you've learned:

• Database connections are live, authenticated connections — not passive file reads. They require credentials and respect SQL Server's permission system.
• Native queries let the database do the heavy lifting. Write your filters, joins, and column selections in SQL, and only the data you need travels to Power Query.
• Query folding is your performance ally. Apply row filters and column selections early in your transformation steps so Power Query can push them back to the server.
• Credentials are stored per data source, not per file. Manage them through Data Source Settings, and be aware that published reports need credentials configured in the Power BI Service separately.
• Privacy levels affect whether cross-source queries fold. Set internal corporate sources to Organizational for the best balance of security and performance.

Where to go next:

• Learn about Power Query Parameters — these let you build dynamic native queries where the user can change a date range or filter value without editing M code.
• Explore connecting to other databases — PostgreSQL, MySQL, and Azure SQL Database all work with nearly identical steps, just different connector dialogs.
• Study Query Folding in depth — understanding exactly which M functions fold and which don't will have a massive impact on report performance as your data grows.
• If your organization uses Power BI Service, investigate On-premises Data Gateway configuration — it's the bridge that makes scheduled refresh work for on-premises databases.

The skills you've built here don't just apply to one report. Every database-backed analysis you build from here will benefit from knowing how authentication works, how to write efficient native queries, and how to diagnose credential problems when they arise. That's foundational competence — and that's what separates a good analyst from a great one.