Value.NativeQuery
Sends a SQL statement you write to a database connection and returns the result as a table.
Value.NativeQuery(target, query, optional parameters, optional options)Returns: A table with the columns the SQL statement returns.
What it does
Value.NativeQuery lets you write the SQL yourself and run it on the server, instead of picking tables and building steps. Use it when a join or an aggregation is clearer in SQL, or when a query needs a database feature that the steps cannot express. The statement is sent as written. Power Query does not change it.
Syntax and arguments
target: the connection, such as the value returned by Sql.Database.query: the SQL statement, as text. Use SELECT statements.optional parameters: values that fill in placeholders in the statement, supplied as a list or record. Leave it out when the statement has no placeholders.optional options: a record of settings for how the statement runs.
Example
A controller wants the shipped orders from the classicmodels sample database, written as a SQL statement so the server returns only the rows needed.
let
Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
Shipped = Value.NativeQuery(Source,
"SELECT orderNumber, orderDate, customerNumber FROM classicmodels.orders WHERE status = 'Shipped'")
in
Shipped
The statement returns the order number, date and customer for every shipped order. The filter runs on the server, and only the answer comes back to the workbook.
Example: a second, different use
Writing a join that is clearer in SQL than in steps, for example order lines with their products, then loading the result.
Things to watch
- Write SELECT statements. Writing data through a query is not what this is for, and the sample databases are read-only.
- Steps after Value.NativeQuery may not fold back to the server. Check View Native Query on each step.
- A statement that works in one SQL engine may not work in another. This is T-SQL, so check the dialect.
- Keep the SQL in a readable layout with a comment at the top, so the next person knows what it was written to do.
In business terms
Finance teams often already have a SQL statement that produces a trusted report. Value.NativeQuery lets Power Query use that same statement, so the workbook and the database agree.