Get Data from SQL Server

Power BI · Getting Data

Theory

The SQL Server connector asks for a server name and, optionally, a database name, then a data connectivity mode: Import or DirectQuery. After you authenticate (Windows or database credentials), the Navigator lists tables and views to pick from. Advanced options let you type a native SQL statement instead.

Behind the scenes Power BI creates an M step Sql.Database("server", "database") and then navigates to the table. Because SQL Server understands SQL, many later Power Query steps such as filtering rows or removing columns are translated back into a SQL query (query folding), so the database does the heavy lifting. For example, filtering Sales to OrderDate in 2024 in Power Query becomes a WHERE clause sent to the server.

Why we use it

Company data usually lives in relational databases. Connecting directly gives you trusted, up-to-date tables and lets the database filter and aggregate before data reaches Power BI.

When to use it

Use Import for most reports: fastest visuals and full DAX. Choose DirectQuery when data is very large or must be near real time. Prefer selecting tables or views over pasting long SQL, because custom SQL can limit folding of later steps.

How it works

  1. Get data → SQL Server
  2. Enter server and database name
  3. Is the data huge or needed in near real time? Yes: Choose DirectQuery. No: Choose Import.
  4. Sign in with Windows or database credentials
  5. Select tables or views in the Navigator
  6. Filter and shape in Power Query (steps fold to SQL)
  7. Load into the model

Syntax

Home → Get data → SQL Server
Server: myserver.database.windows.net   Database: SalesDB
Data connectivity mode: Import | DirectQuery

M:
Sql.Database(server as text, database as text, optional options as record)
Sql.Database("srv", "SalesDB", [Query = "SELECT ..."])

Example

let
    Source = Sql.Database("sql-prod-01", "SalesDB"),
    Sales = Source{[Schema = "dbo", Item = "Sales"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Sales, each [OrderDate] >= #date(2024, 1, 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",
        {"OrderID", "OrderDate", "CustomerID", "ProductID", "Region", "Qty", "Amount"})
in
    #"Removed Other Columns"

// Right-click the last step → View Native Query to see the SQL it folds to.

Key points

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic