Sql.Database
Connects to a SQL Server database and returns a navigation table from which you pick the tables or views to load.
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
server: the server name, with the port if it is not the default, such as"learnsql.cpetoday.com,1433".database: the database name, such as"classicmodels".optional options: a record of settings. The most useful key isQuery, which sends a SQL statement you write (see Value.NativeQuery).CreateNavigationProperties = falsestops Power Query from adding links between related tables, which can make a very large navigation list easier to handle.
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
- Credentials. The first time, Power Query asks how to sign in (Windows, Database, or a Microsoft account, depending on the server). It stores the choice per data source. Use the connection guide on this site for the sign-in method this server uses.
- Privacy levels. Each source has a privacy level (None, Private, Organizational or Public). Mixing sources with different levels in one query can block a step with a Formula.Firewall error. Set the levels in Data source settings.
- Query folding. Filters, column choices and simple groups usually fold into SQL. Steps that cannot fold pull every row to your PC. Right-click a step and check View Native Query.
- Schema. On this server the schema name is the database name, so the item is found under
[Schema = "classicmodels"].
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.