Sql.Database
Accessing DataReturns a navigation table of tables and views from a SQL Server database.
Syntax
Sql.Database(server as text, database as text, optional options as nullable record) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
server | text | Yes | The SQL Server hostname, IP address, or named instance (e.g., "myserver.database.windows.net" or "localhost\SQLEXPRESS"). |
database | text | Yes | The name of the database to connect to on the SQL Server instance. |
options | record | No | An optional record with connection options such as CommandTimeout, CreateNavigationProperties, HierarchicalNavigation, ConnectionTimeout, Query, and SqlCompatibleWindowsAuth. |
Return Value
table — A navigation table of tables and views from the specified SQL Server database.
Remarks
Sql.Database connects to a SQL Server instance and returns a navigation table of the tables, views, and other database objects in the specified database. It supports SQL Server on-premises (any supported version), Azure SQL Database, Azure SQL Managed Instance, and SQL Server running in Azure VMs. The navigation table is organized by schema; reference a specific table using {[Schema="schemaName", Item="tableName"]}.
Authentication: Three authentication modes are supported, configured in the data source credentials dialog — not in the M query: - Windows authentication — uses the credentials of the signed-in Windows user or gateway service account. - Database authentication — SQL Server username and password. - Microsoft Account (Azure AD) — for Azure SQL Database and Azure SQL Managed Instance. Power BI Desktop handles the interactive sign-in flow.
Do not embed connection credentials in the M query. Use the credentials dialog to store them securely.
Query folding: SQL Server is a fully foldable data source. Table.SelectRows, Table.SelectColumns, Table.Sort, Table.Group, joins, and most other Power Query transformations are translated into SQL and executed on the SQL Server — only the result is returned to Power Query. This is critical for performance on large tables. To bypass folding and run a specific SQL statement, use the Query option or Value.NativeQuery.
Key options:
- CommandTimeout (duration) — maximum time to wait for a query to complete before timing out.
- ConnectionTimeout (duration) — maximum time to wait when establishing the initial connection.
- CreateNavigationProperties (logical) — generate navigation properties from foreign key relationships. Defaults to true.
- HierarchicalNavigation (logical) — group the navigation table by schema name. Defaults to false.
- Query (text) — execute a native T-SQL query directly instead of navigating to a table.
- SqlCompatibleWindowsAuth (logical) — force SQL Server-compatible Windows authentication mode (useful in some gateway scenarios).
Power BI Service: Azure SQL Database supports direct cloud refresh without a gateway. On-premises SQL Server requires an on-premises data gateway.
Examples
Example 1: Connect to a local SQL Server database
```powerquery
Sql.Database("localhost", "AdventureWorks")Example 2: Connect to Azure SQL Database
```powerquery
Sql.Database("myserver.database.windows.net", "SalesDB")Example 3: Navigate to a specific table in a schema
```powerquery
let
Source = Sql.Database("myserver.database.windows.net", "SalesDB"),
Orders = Source{[Schema="dbo", Item="Orders"]}[Data]
in
OrdersExample 4: Run a native T-SQL query using the Query option
```powerquery
Sql.Database(
"localhost",
"AdventureWorks",
[Query = "SELECT TOP 100 SalesOrderID, OrderDate, TotalDue FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC"]
)Example 5: Enable hierarchical navigation and set a command timeout
```powerquery
Sql.Database(
"localhost",
"AdventureWorks",
[
HierarchicalNavigation = true,
CommandTimeout = #duration(0, 0, 5, 0)
]
)