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
- Get data → SQL Server
- Enter server and database name
- Is the data huge or needed in near real time? Yes: Choose DirectQuery. No: Choose Import.
- Sign in with Windows or database credentials
- Select tables or views in the Navigator
- Filter and shape in Power Query (steps fold to SQL)
- 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
- Import mode copies data into Power BI's in-memory model
- DirectQuery keeps data in the source and queries it live
- Requires server name and (optionally) database name to connect
- Filters and column removal usually fold back to SQL
- Scheduled refresh of an on-premises server needs a data gateway