SQL variables in `Value.NativeQuery()'

15 Sep, 2026

I like being able to throw SQL queries straight from Power Query into a data source, for a lot of reasons[1].

I recently discovered that you can use SQL variable syntax as well! I used to use string concatenation, but this is a lot prettier and more elegant.

The basic form of calling the variable is using @, just like in normal SQL.

SELECT
    @constant as Something
FROM dbo.sometable

That constant is defined as an options record in Value.NativeQuery():

Value.NativeQuery(
    database,
    "SELECT
        @constant1 as Something
        , @constant2 as SomethingElse
    FROM dbo.sometable",
    [
        @constant1 = "Something",
        @constant2 = "Something Else"
    ]
)

And voila! You can refactor this to your heart's content.

[1] Mainly because it saves on a lot of processing steps, mainly joins, but also renaming and reordering columns and other simple column calculations.

#excel #powerbi #powerquery #sql