Value.NativeQuery

Value

Executes a native query (e.g., SQL) against a data source, bypassing M query folding.

Examples on this page use shared sample tables. View them to understand the input data before reading the examples below.

Syntax

Value.NativeQuery(target as any, query as text, optional parameters as any, optional options as nullable record) as any

Parameters

NameTypeRequiredDescription
targetanyYesThe data source or connection against which the native query is executed (e.g., a SQL database connection or table).
querytextYesThe native query string (e.g., a SQL SELECT statement).
parametersanyNoAn optional record or list of parameter values for parameterized queries.
optionsrecordNoAn optional record of additional options, such as [EnableFolding = true] to allow query folding on top of the native query result.

Return Value

any — The result of executing the native query against the target data source.

Remarks

Value.NativeQuery sends a native query string (typically SQL) directly to a data source, bypassing M's query folding pipeline. Use it when:

- You need database-specific syntax that M cannot express or fold (e.g., stored procedure calls, window functions, CTEs with specific hints) - You want precise control over the exact query plan executed at the source - Performance requires a hand-tuned query that M's generated SQL would not produce

The target is typically a database connection object (such as Sql.Database("server", "db")) or a table from within that connection.

Security warning — SQL injection risk: Never construct the query string by concatenating user-supplied input. Use the parameters argument to pass values safely. Parameterized queries are supported on most connectors and are the correct way to incorporate dynamic values.

By default, M cannot fold further operations on top of a native query result — filtering, sorting, or joining in M will be applied in-memory after retrieving the full result. To allow the engine to fold additional operations on top of the native query, pass [EnableFolding = true] in the options record (only supported by connectors that implement this capability).

Note that Value.NativeQuery is not supported in Excel Online (see compatibility). Also note that the function may require query approval in Power BI Desktop depending on data source settings.

Examples

Example 1: Execute a SQL query against a SQL Server database

let
    Source = Sql.Database("myserver", "AdventureWorks"),
    Result = Value.NativeQuery(
        Source,
        "SELECT TOP 10 ProductID, Name, ListPrice FROM Production.Product ORDER BY ListPrice DESC"
    )
in
    Result
Applied Steps

The final output — executes the native SQL query against the database and returns the top 10 products ordered by ListPrice descending.

ProductID
Name
ListPrice
1749Road-150 Red, 623,578.27
2750Road-150 Red, 443,578.27

Example 2: Parameterized query — safe way to include dynamic values

let
    Source = Sql.Database("myserver", "AdventureWorks"),
    MinPrice = 1000,
    Result = Value.NativeQuery(
        Source,
        "SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ListPrice >= @MinPrice",
        [MinPrice = MinPrice]
    )
in
    Result
Applied Steps

The final output — executes the parameterized SQL query with @MinPrice bound to 1000, returning products with ListPrice at or above that threshold.

ProductID
Name
ListPrice
1749Road-150 Red, 623,578.27
2750Road-150 Red, 443,578.27

Example 3: Enable folding on the native query result

let
    Source = Sql.Database("myserver", "AdventureWorks"),
    NativeResult = Value.NativeQuery(
        Source,
        "SELECT ProductID, Name, ListPrice FROM Production.Product",
        null,
        [EnableFolding = true]
    ),
    Filtered = Table.SelectRows(NativeResult, each [ListPrice] > 500)
in
    Filtered
Applied Steps

The final output — applies a row filter in M to keep only products with ListPrice above 500; because folding is enabled, this filter may be pushed to the SQL source.

ProductID
Name
ListPrice
1749Road-150 Red, 623,578.27
2750Road-150 Red, 443,578.27

Related Functions

Compatibility

✓ Power BI Desktop✓ Power BI Service✓ Excel Desktop✗ Excel Online✓ Dataflows✓ Fabric Notebooks