Your First Power BI Connection: How to Import SQL Server Data Without Breaking Query Folding

Light

A single careless step when connecting Power BI Desktop to SQL Server can silently sabotage query folding or introduce credential headaches that only show up at refresh time. By the end of this guide, you’ll understand the trade-offs in every choice and avoid the common traps that slow down models before the first visual is built.

Prerequisites and Decisions Before You Click “Get Data”

The first error most new connections make is skipping the initial planning: not every SQL Server is set up for the same authentication, and not every database schema is shaped for folding or efficient import. Before opening Power BI Desktop, confirm:

• Authentication: Do you have Windows/Integrated security access, or will you need SQL Server authentication? If you need SQL credentials, having them in advance avoids an immediate dead-end.
• Network Path: Can you reach the SQL Server from your machine? Firewalls or VPNs blocking TCP 1433 (or the specific port) will halt you at the connection dialog—with a generic network error that rarely mentions the real cause.
• Data Volume and Shape: Are you importing massive fact tables, or just a handful of reference tables? If the row count is in the tens of millions or more, plan for Import vs DirectQuery up front. DirectQuery radically changes what’s possible: no VertiPaq cache, and query folding is not just nice-to-have, it’s survival.

Failing to check these before import leads to the classic “it worked in Desktop, but fails in the Service” scenario.

Step-by-Step: Connecting and Choosing Import vs DirectQuery

1. Start with “Get Data” → “SQL Server”.

   Non-obvious: If you use the “SQL Server Database” connector (not OLE DB or ODBC), you maximize query folding and future compatibility. Choosing OLE DB/ODBC here—not because you have to, but because it seems generic—breaks folding at the first step. Only use OLE DB/ODBC if your SQL Server version, driver, or security requirements demand it and you’re prepared for the trade-off.

2. Enter Server and Database.

   You can specify just the server, then pick the database later. If you’re on a named instance or a non-default port, use syntax like MyServer\SQLEXPRESS,1433—missing the comma/port will produce a generic “server not found” error that doesn’t mention ports at all.

3. Choose Data Connectivity Mode: Import or DirectQuery.

   This is the fork in the road. Import loads data into VertiPaq and enables full DAX capabilities and speed—but it can quietly eat all your memory or blow up refresh times with large tables. DirectQuery leaves the data in SQL Server and issues live queries, but disables or restricts many DAX functions (including most time intelligence) and is far less forgiving of inefficient queries or non-foldable steps. Once you pick and load, switching later is a complete reload and often a model rewrite—plan accordingly.

4. Authentication: Pick the mechanism your SQL Server expects.

   Non-obvious: If your organization uses Single Sign-On (SSO) and you select SQL Authentication (username/password), you may find refreshes work in Desktop but fail in the Service—where Kerberos delegation or OAuth are needed. Always check what the Service account will use in production, not just what works for you locally.

5. Navigator: Pick only what you need—no more, no less.

   Importing every table in the database is an easy trap; it bloats your model, increases refresh times, and can break folding in Power Query if your joins or transformations aren’t foldable. If you’re unsure, start small and add later.

Query Folding: The Invisible Saboteur

The most common cause of slow refreshes or DirectQuery pain is a transformation in Power Query that breaks folding—often without warning. Query folding means Power BI pushes as much filtering, joining, and transformation logic as possible back to SQL Server, rather than materializing data in-memory and transforming row-by-row in M. The moment folding is broken, you lose server-side horsepower and saddle Power BI with the work.

Here’s the subtlety: not all Power Query steps are obvious folding-breakers. For example, using Table.AddIndexColumn or Table.Buffer will immediately terminate folding, as will most custom M functions—even if the step “looks” trivial.

Worked Example: The Trap of Adding an Index Column

Take a model with a Fact_Sales table of 10M rows in SQL Server. Suppose you want to add a sequential index in Power Query:

// Naive approach (breaks folding)
let
    Source = Sql.Database("SQLServer01", "SalesDB"),
    Fact_Sales = Source{[Schema="dbo", Item="Fact_Sales"]}[Data],
    // The following step breaks folding completely
    WithIndex = Table.AddIndexColumn(Fact_Sales, "RowNum", 1, 1)
in
    WithIndex

This looks harmless, but Table.AddIndexColumn is not translatable to SQL—so Power Query pulls all data down first, then adds the index. On a large table, this balloons refresh times and memory usage, and in DirectQuery mode, it simply fails (since DQ tables can’t have non-foldable steps).

The fix: Add the index in SQL directly (as an identity column or via a view), or—if you must do it in Power Query—accept the folding break and apply it only to a small, non-fact table. Always check folding status: right-click a step and check “View Native Query”; if it’s greyed out, folding has already broken.

Credential Pitfalls: Why “It Works in Desktop” Isn’t Enough

One of the most misleading Power BI behaviors is that Desktop and Service handle credentials differently—especially for Windows/Integrated authentication. You may be able to import and refresh data locally using your own credentials, but when you publish to the Service, refreshes can fail with cryptic errors unless the Service (or its gateway) is configured for the same authentication and has network access to the SQL Server.

• Gateway Required: If your SQL Server is on-premises and your Power BI Service workspace isn’t running in the same network, you must configure an On-premises Data Gateway.
• Credential Mapping: The Service never uses your personal Windows credentials for scheduled refreshes. If you used Integrated authentication in Desktop, but don’t map it via the Gateway, refresh fails.
• OAuth and SSO: As of current versions, OAuth is supported for SQL Server in certain scenarios but not all. If you need SSO, check compatibility and test on the Service before promising a solution.

Don’t trust a successful local refresh as proof your setup is ready for production.

Advanced: Filtering at Source vs. In Power Query

The most overlooked performance lever is where you filter data. Filtering in Power Query’s UI can fold to SQL, but only if the step is foldable and placed before any folding-breaker. If you apply a filter after a custom function or Table.Buffer, it happens locally—on all rows, post-download.

For example, suppose you want only sales from 2023:

// Good: filter folds, only 2023 data pulled from SQL
let
    Source = Sql.Database("SQLServer01", "SalesDB"),
    Fact_Sales = Source{[Schema="dbo", Item="Fact_Sales"]}[Data],
    Filtered = Table.SelectRows(Fact_Sales, each [SaleDate] >= #date(2023,1,1) and [SaleDate] < #date(2024,1,1))
in
    Filtered

But if you buffer before filtering:

// Bad: filtering happens after all rows are in memory
let
    Source = Sql.Database("SQLServer01", "SalesDB"),
    Fact_Sales = Source{[Schema="dbo", Item="Fact_Sales"]}[Data],
    Buffered = Table.Buffer(Fact_Sales), // breaks folding
    Filtered = Table.SelectRows(Buffered, each [SaleDate] >= #date(2023,1,1) and [SaleDate] < #date(2024,1,1))
in
    Filtered

This second pattern reads all rows into memory, then filters—undoing any performance benefit of folding. The fix is to avoid Table.Buffer unless you’re working with a truly small table and folding is impossible, or you have a specific need to “freeze” a query result. Always check folding status after each step.

What “Done” Looks Like: Verifying a Healthy Connection

• Query folding holds until the final step for each Import/DirectQuery table.
  
  Check “View Native Query” is available and shows a valid SQL statement for the last transformation step.
• Scheduled refresh works in the Service/Gateway without credential errors.
  
  Publish the report, configure credentials and gateway as needed, and trigger a refresh. Only consider the connection ready when this passes.
• Data volume and shape are as expected.
  
  Row counts, column data types, and null handling match what you expect from the source—no silent truncation or type changes.

Actionable Checklist: Steps to Avoid Common SQL Server Import Mistakes

• Use the native SQL Server Database connector, not OLE DB/ODBC, unless forced by requirements.
• Decide on Import vs DirectQuery before loading; switching later is a rework, not a toggle.
• Confirm authentication requirements for both Desktop and Service; test refresh in the Service early.
• Minimize transformations in Power Query; keep all filters and joins as early as possible and check folding after every step.
• Never use Table.Buffer, Table.AddIndexColumn, or custom M functions on large/fact tables unless folding is already broken and the table is small.
• After import, verify data shape, row counts, and refresh behavior in the published workspace—not just in Desktop.

The difference between a reliable, performant Power BI model and a future headache is almost always decided at the connection and query folding steps. Taking the time to check folding, authentication, and refresh paths at the start saves hours of troubleshooting and model rewrites later. If you run into an error, don’t just patch it in Desktop—trace it through to the Service and Gateway, and always verify folding at every transformation step.