Sql.Database

A SQL Server or Azure SQL database: its tables and views to navigate, or the rows of a SQL query you write.

Group: Databases and lakes. Power Query M has it too: a query that uses it is written as in Power BI.

Syntax

Sql.Database(server as text, database as text, optional options as nullable record) as table

Returns

table. Without Query, a navigation table: a row per table or view, with Name, Data, Schema, Item and Kind, or a row per schema (Schema, Data) with HierarchicalNavigation. With Query, the statement's rows.

Remarks

• A table is found by its schema and name, as Get data writes it: Source{[Schema = "dbo", Item = "Orders"]}[Data]. Kind is "Table" or "View"; the rows are in order of schema, then name. Name is the table's name, with its schema in front outside dbo: sales.Orders.
• With HierarchicalNavigation = true the first level is the database's schemas, every one it has (dbo, guest, INFORMATION_SCHEMA, sys and its own), keyed by Schema; a schema's Data lists its tables and views (Name, Data, Kind), keyed by Name. The system schemas list nothing.
• OpenBI reads SQL Server through an ODBC driver for SQL Server on the computer, Microsoft ODBC Driver 18 first; the error says so when there is none.
• A table's Data is the whole table, read when it is used; the steps after it run in DuckDB on the rows read. To have SQL Server filter, join or group first, write that SQL in Query.
• Sign in with Windows, a SQL Server user, or Microsoft Entra ID (Azure SQL starts with Entra ID). When the server's certificate isn't trusted, the error offers to trust it and connect: do it only for a server you know.
• Credentials are never part of the M: the studio asks for a sign-in the first time this computer reads the source and keeps it on this computer only, for you.

Compared with Power Query

Of Power Query's options Query and HierarchicalNavigation are read; the others (CommandTimeout and the rest) are accepted and change nothing. Power BI Desktop also lists a schema's functions, with Kind "Function"; OpenBI lists the tables and views.

Examples

Down to the orders table

let
    Source = Sql.Database("sql.norwayanalytics.no", "Sales"),
    Orders = Source{[Schema = "dbo", Item = "Orders"]}[Data]
in
    Orders

Result: not shown here: it needs a SQL Server and a sign-in.

A query the server runs

Sql.Database("sql.norwayanalytics.no", "Sales", [Query = "SELECT Shop, SUM(Amount) AS Sales FROM dbo.Orders GROUP BY Shop"])

Result: not shown here: it needs a SQL Server and a sign-in.

The result has the statement's columns, Shop and Sales.

Schema by schema

let
    Source = Sql.Database("sql.norwayanalytics.no", "Sales", [HierarchicalNavigation = true]),
    Sales = Source{[Schema = "sales"]}[Data],
    Orders = Sales{[Name = "Orders"]}[Data]
in
    Orders

Result: not shown here: it needs a SQL Server and a sign-in.

Power BI Desktop writes these steps when Navigate using full hierarchy is ticked.

Related functions

Sql.Databases, Odbc.DataSource, PostgreSQL.Database, Table.SelectRows.

Last updated 8 November 2026 · OpenBI Studio 0.3.79