<!-- https://learnsql.cpetoday.com/functions/m/value-nativequery -->
# Value.NativeQuery

Language: Power Query M
Category: Data sources
Syntax: Value.NativeQuery(target, query, optional parameters, optional options)
Summary: Sends a SQL statement you write to a database connection and returns the result as a table.
Returns: A table with the columns the SQL statement returns.
Level: Advanced
Related: Sql.Database, Sql.Databases, Table.SelectRows, Table.Group, List.Sum

## 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.

```powerquery
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.
