Downloads
Power Query MData sourcesAdvanced

Value.NativeQuery

Sends a SQL statement you write to a database connection and returns the result as a table.

Syntax
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

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

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.

Related functions

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