Downloads
Power Query MData sourcesBeginner

Sql.Database

Connects to a SQL Server database and returns a navigation table from which you pick the tables or views to load.

Syntax
Sql.Database(server, database, optional options)

Returns: A navigation table. Selecting a table from it gives you that table's rows.

What it does

Sql.Database opens a connection to a SQL Server database and gives you a list of the schemas, tables and views it holds. You pick the one you need, and the query loads its rows. It is the first line of most queries that read from a database. The connection is not made until the query runs, and the steps after it decide how much of the work the server does (see query folding below).

Syntax and arguments

Example

An accountant wants the orders of the classicmodels sample database, shipped orders only, with the order number, date and customer.

let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
    Orders = Source{[Schema = "classicmodels", Item = "orders"]}[Data],
    Shipped = Table.SelectRows(Orders, each [status] = "Shipped"),
    Kept = Table.SelectColumns(Shipped, {"orderNumber", "orderDate", "customerNumber"})
in
    Kept

Source is the navigation table. The [Schema] and [Item] lookup picks the orders table, and [Data] gives its rows. The two steps after it filter and trim the columns, and both fold back to the server, so the database does the work.

Example: a second, different use

Sending a native SQL statement with the Query option: Sql.Database("learnsql.cpetoday.com,1433", "classicmodels", [Query = "SELECT orderNumber, orderDate FROM classicmodels.orders WHERE status = 'Shipped'"]).

Things to watch

In business terms

Pulling ledger, invoice, payment or inventory tables from a company database into a workbook or a Power BI model is the most common Power Query task. Keep the filters early so the database does the heavy lifting.

Related functions

All Power Query M functions · View as Markdown · M basics