- Value Function | Power Query M

Value.NativeQuery is a Power Query M function that evaluates a query against a target, using specified parameters and options. The function returns the output of the query as defined by the target, with the query expressed in a manner specific to the target.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

Evaluates query against target using the parameters specified in parameters and the options specified in options.

The output of the query is defined by target. Target provides the context for the operation described by query.

Query describes the query to be executed against target. query is expressed in a manner specific to target (for example, a T-SQL statement).

The optional parameters value may contain either a list or record as appropriate to supply the parameter values expected by query.

The optional options record may contain options that affect the evaluation behavior of query against target. These options are specific to target.

Examples

Imagine you have a connection to your database.

let 
  Source = Sql.Databases( "localhost" ),
  AdventureWorksDW2023 = Source
    { [Name = "AdventureWorksDW2023"] }
    [Data]

Enable Query Folding

You can use the Value.NativeQuery function to run a SQL query directly to your database. What’s interesting is that this function allows you to set an option to run your query in a way that allows query folding. Do do that you simply set EnableFolding = true in the third argument.

Value.NativeQuery(
  AdventureWorksDW2023, 
  "Select * From DimProduct", 
  null, 
  [
    EnableFolding = true
  ]
  )

Any subsequent steps that follow here have the chance to fold. This is still reserved to only some actions, because connectors don’t support folding all Power Query operations.

PreserveTypes

Sometimes it may be useful to preserve data types from your database.You can do that by setting PreserveTypes = true.

Value.NativeQuery(
  AdventureWorksDW2023, 
  "Select * From DimProduct", 
  null, 
  [
    PreserveTypes = true, 
    EnableFolding = true
  ]
  )

Learn more about Value.NativeQuery in the following articles:

Query Folding On SQL Queries In Power Query Using Value.NativeQuery() and EnableFolding=true
This article discusses how you can use Value.NativeQuery and still enable Query Folding on SQL Queries. » Read more
Preserving Data Types With SQL Queries In Power Query And Power BI
Preserve data types by setting the PreserveTypes options in the Value.NativeQuery function. » Read more
Query Folding on Native Queries
This article delves into using Query Folding for Native Queries. It also provides a list of connectors that currently supports this. » Read more

Used by

While you can use the Value.NativeQuery function by itself, it also works together with:

Other functions related to Value.NativeQuery are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/value-nativequery