Passing Parameters To SQL Queries With Value.NativeQuery() In Power Query And Power BI

I first came across the Value.NativeQuery() M function about six months ago, but it didn’t do anything useful then so I didn’t blog about it. I checked it again recently though and now it does something very handy indeed: it allows you to pass parameters to SQL queries. Before this, if you wanted to use parameters in your SQL, you had to do some nasty string manipulation in the way Ken Puls shows here. Now, with Value.NativeQuery(), you can handle SQL query parameters properly in M.

Here’s a simple example that shows how it works, passing two parameters to a SQL query on the Adventure Works DW database:

    Source = Sql.Database("localhost", "Adventure Works DW"),
    Test = Value.NativeQuery(
            "SELECT * FROM DimDate
            WHERE EnglishMonthName=@MonthName
            [MonthName="March", DayName="Tuesday"])


Some explanation of what’s happening here:

  • The Source step returns a reference to a SQL Server database, and this is passed to the first parameter of Value.NativeQuery().
  • The second parameter of the Value.NativeQuery() function is the SQL query to be executed. It contains two parameters called @MonthName and @DayName.
  • The parameters to the SQL query are passed using a record in the third parameter of Value.NativeQuery(). Note how the named of the fields in the records match the names of the parameters in the SQL query.

It looks like, eventually, this will be the way that any type of ‘native’ query (ie a query that you write and give to Power Query, rather than a query that is generated for you) is run against any kind of data source – instead of the situation we have today where different M functions are needed to run queries against different types of data source. I guess at some point the UI will be updated to use this function. I don’t think it’s ‘finished’ yet either, because it doesn’t work on Analysis Services data sources, although it may work with other relational data sources – I haven’t tested it on anything other than SQL Server and SSAS. There’s also a fourth parameter for Value.NativeQuery() that can be used to pass data source specific options, but I have no idea what these could be and I don’t think there are any supported for SQL Server. It will be interesting to see how it develops over the next few releases.

[Update February 2021]

For relational databases that use question marks (?) instead of @ names for parameter placeholders, you need to use Value.NativeQuery in a slightly different way by passing a list of values rather than a record to the second parameter. Here’s an example:

  "SELECT * FROM DimDate
   WHERE EnglishMonthName=?
   {"March", "Tuesday"})


62 responses

  1. Hey! This is phenomenal for people that like using Stored Procedures. Didn’t even know you could do it this way. I’d always just try to create the SQL query needed at the cell level of the excel spreadsheet and have PQ read the values from that table against the invoke of Sql.Database.

    Good stuff, Chris!

  2. Great blog Chris. Could you not create a parameter and then pass this into the third parameter of the Value.NativeQuery()

    For example if you have a parameter called “Day Name” it could then be:

    Source = Sql.Database(“localhost”, “Adventure Works DW”),
    Test = Value.NativeQuery(
    “SELECT * FROM DimDate
    WHERE EnglishMonthName=@MonthName
    [MonthName=”March”, DayName=#”Day Name”])

    • Hi Gilbert,
      Its nice post it helps a lots, but still have some doubts.

      I came to a situation, where i need to show data only for current logged in user.
      I have a sp
      @StartDate Date,
      @EndDate Date,
      @ManagerID INT
      StartDate and EndDate default it will load for 0 days, but once user select date from power BI UI same date will be passed. ManagerID will be user_name(), or user_princplename()
      Please suggest how we can achive this.

  3. Pingback: Dew Drop - December 12, 2016 (#2381) - Morning Dew

  4. Actually I don’t understand what this query gives to me. where I should I define the parameters? could the parameters could change by the slicers in power bi reports?

    • No, I don’t think so – slicers are for filtering data that has already been loaded into the model and is being displayed in a report. However I do think it will be possible for end users to change parameter values and reload data in the future.

      • Thank you Chris
        OK. But if the parameter values are stored in a table they will be loaded and visible in a slicer. Maybe that has to be a “different” kind of slicer as the action is to reload the data and not to filer the already loaded data. To let the user select and load a set of data in a big database for analysing in details is very useful. I know from personal experience.

  5. Hi Chris
    Awesome Post!
    I am trying to pass a list using the IN clause (@mylist) but it does not work when i use

    Is there a certain way to pass a list in this way? any help would be appreciated

  6. Hi Chris
    Thanks for responding.
    Currently i have a SQL query that uses a where clause. for example

    select * from sales
    product in (‘123′,’124′,’125′,’126’)
    this list is currently hard coded in the SQL

    However, i would like to be able for the user to update a table in excel, hit refresh and the above list of products is dynamically updated with the new list.

    I have been able to get this to work using one of Ken’s post for a single criteria and concatenating the value into the SQL but can not find anything that deals with the above scenario of a list.

  7. and here is the current actual M code with the stock code list


    Source = Sql.Database(“SQL”, “DB”),
    Connection = Value.NativeQuery(Source,
    DET_STOCK_CODE as ‘StockCode’
    ,STKNAME as Description
    ,sum(DET_GROSS) as ‘Gross’
    ,sum(DET_NETT) as ‘Nett’
    ,sum(DET_QUANTITY) as ‘Quantity’
    ,oh_user2 as ‘Season’

    oh_user2 in (‘2016′,’2015′,’2014’)
    and DET_STOCK_CODE in (‘140961′,’140950′,’140961′,’140988′,’141053′,’141061′,’141088′,’170045′,’170070′,’170080′,’170083′,’170091′,’170121′,’170172′,’170180’,’170199

    group by

  8. Hi Mike,
    “However, i would like to be able for the user to update a table in excel, hit refresh and the above list of products is dynamically updated with the new list.”
    You can do this with VBA and ADODB. Excel acts like a front end to the database.
    If you like i can help you.

  9. Hi GostaM
    Thank you for your offer. I would prefer to use M code for this as it might be something i use within PowerBi desktop and the list is an external file.
    Kind Regards

  10. I’m wondering why in the third parameter you are declaring the variables with hard coded values (“March”, “Tuesday”) instead of references to Power BI parameters? What’s the benefit of doing this?

    • There isn’t any benefit to doing this – I just wanted to make the example as easy to understand as possible. In the real world you probably would use Power BI parameters to supply these values.

      • Oh right, makes sense. I don’t know how much you’ve experimented with this function, but I’m not sure if it’s actually a valid route to passing parameters to SQL queries. I was able to make my parameterized query work within the Power BI ‘Query Editor’ but it refuses to import it to the ‘Report’ view once I hit ‘Close & Apply’:

        “Mixing DirectQuery and imported data is not supported.”

        “Microsoft SQL: Must declare the scalar variable “@DateBegin”.


      • It sounds like you have another problem here, not related to the code: as the error message says, you can’t have one data source in DirectQuery mode and another that tries to import data into the data model. Can you try this in a completely new .pbix file with no other queries in?

  11. That’s exactly what did the trick, a totally blank new .pbix file removed my error messages. I actually ended up not using the function you suggested in this, I just used the “default” generated Sql.Database()-function. I set two variables to my parameters and put them in the middle of the SQL statement string with “&VariableName&”.

    Here’s the code I used:

    Thanks for taking time to answer my comments 🙂

    • This is a really old post and Q & A but it is such a good un’ that I have returned to it multiple time over the years just for reference. In case others come across this small issue …

      “Microsoft SQL: Must declare the scalar variable “@Date”, and you are getting your date value from a parameter, you might be passing a Datetime parameter, rather than a Date parameter. Check your parameter type and the expected SQL parameter type match.

      As Date and DateTime are not the same thing, it looks like the SQL parameter isn’t being generated when running the report, but annoyingly (many years later) the Power Query transformation editor is happy to work with it.

  12. Hi All,

    I Have Stored procedure along with Parameter (Date Parameter) . I have created Parameter in Power BI Desktop and passing parameter value to stored procedure Dynamically .

    Here my question is :

    1) Always I’m using “edit query” -> “edit parameter” to pass value to my stored procedure to fetch the records and along with by clicking “Refersh data” warning message .

    Currently, I am passing the Date value to the Parameter maually in Power BI deskTop to get the report updated .

    Could you Please help if there is a way to dynamically pass this date from a date picker selection to the Stored procedure parameter.

    Here I’m using the created parameter date as my i/p parameter value in desktop .


  13. Thanks Ragu,
    Your question is exactly what I am looking for to.
    The parameter can be anything like a cost center so you from a “picker” or a slicer in Power BI can select/reduce the amount of details imported to Power BI. Currently I have an application with a proper API which can do that.

    • In Power BI Desktop you can’t do this (although in Excel you can) unfortunately – the only way to change a parameter value is through the Edit Parameter dialog; after you have published to the Power BI you can’t change a parameter at all. You need to import all the data your user ever needs to see in their reports into the Data Model, or if you have too much data use DirectQuery mode or a Live connection to SSAS.

  14. Thanks Chris,
    As per your words , after published we are unable to pass parameter like as in Desktop.
    But I need help from you . I have 3 years of Transaction data and I want to analyse previous years of data as well .


  15. Thanks Chris,
    Yes i know what you say but I do have some future hope for this feature.
    I have tested a work around for this issue which is pretty nice. In Power Pivot with a proper API it is possible to create dynamic databases and DAX forms with subsets of data in the Power Pivot database saved in Excel files.
    In a fileserver the user (a salesmen in this case) can upload his Excel file to a Power BI “template” with a standard set of slicers grafs and matrix reports operating on the subset of a database. He can save that template with a database in “his” name. His Excel fil with the Power Pivot database will be automatically refreshed with the standard scheduler. The only thing the salesmen has to do is to manually refresh his Power BI desktop file which is easy to understand.


  16. Pingback: Data Privacy Settings In Power BI/Power Query, Part 3: The Formula.Firewall Error – Chris Webb's BI Blog

  17. Pingback: Using Your Own SQL Queries For Tables With Modern Data Sources In SSAS 2016+ And Azure Analysis Services – Chris Webb's BI Blog

  18. Pingback: Power Query function-based parameter | MS Excel | Power Pivot | DAX | SSIS |SQL

  19. Dear Chris,
    Thank you so much for your post. It is a life saver. I’ve been struggling for days how to pass a “from date” and “to date” parameter from excel onto a sql query.
    Every other time I was getting an error converting date or date time from varchar…I’ve checked so many website and finally, I tried your solution and it worked like a charm.

    Thanks again

  20. I tried doing this with an ODBC datasource but got an error saying it’s not supported…

    dataSource = Odbc.DataSource ( “dsn=BLP800CFBL” ),
    d0 = yyyymmdd(#”Period”{0}[From]),
    d1 = yyyymmdd(#”Period”{0}[To]),
    custGroupFlag = if CustomerGroups = “” then “–” else “/**/”,
    selectedWHRegionFlag = if selectedRegionValues = “” then “–” else “/**/”,
    WHregionValues = selectedRegionValues,
    selectedSalesRegionFlag = if selectedSalesRegionValues = “” then “–” else “/**/”,
    SalesRegionValues = selectedSalesRegionValues,
    params = [
    #”:from” = Text.From(d0),
    #”:to” = Text.From(d1),
    #”:custGroups” = custGroupFlag,
    #”:selectedRegion” = WHregionValues,
    #”:flagSselectedRegion” = selectedWHRegionFlag,
    #”:selectedSalesRegion” = SalesRegionValues,
    #”:flagSselectedSalesRegion” = selectedSalesRegionFlag
    test = Value.NativeQuery ( dataSource , #”Base Item Structure Component SQL”, params )


  21. This works fine for me as long as I’m supplying literal values instead of parameters. When I switch to parameters, it works in the preview but stops working when I hit close and apply:

    Apply Query Changes:
    Microsoft SQL: Must declare the scalar variable “@rStart”

    THIS WORKS (for both Preview and in the Report):
    Source = Sql.Database(Server, Database),
    Query = Value.NativeQuery(
    @rStart RangeStart
    ,@rEnd RangeEnd”,
    [rStart=”2020-11-20″, rEnd=”2020-11-24″]

    THIS only works in Preview Mode
    Source = Sql.Database(Server, Database),
    Query = Value.NativeQuery(
    @rStart RangeStart
    ,@rEnd RangeEnd”,
    [rStart=@RangeStart, rEnd=@RangeEnd]

    Any idea what’s causing this to fail?

    • Hi Andrew,

      I’m receiving a similar error and as far as I can tell from testing, it only generates when using Direct Query mode. Import mode with identical logic seems to be okay. Were you running in direct or import mode when you received the error message?

      My error message (direct query mode):

      Microsoft SQL: Incorrect syntax near the keyword ‘EXEC’. Must declare the scalar variable “@Warehouse”.


      • Hey Sean

        I was in Import mode.

        I managed to hack my way around my issue and learned that it had to do with Power BI injecting SQL when it encounters @RangeStart and @RangeEnd params (which are required for Incremental Refresh)

  22. Pingback: Chris Webb's BI Blog: Query Folding On SQL Queries In Power Query Using Value.NativeQuery() and EnableFolding=true Chris Webb's BI Blog

  23. What is the difference between using Value.NativeQuery and Sql.Database when importing data?, wich is better?. Because I can write queries in both.

      • I mean: SELECT @field WHERE etc. and I’d like to pass the required field(s) through the @field parameter somehow. The use of parameters in the WHERE and LIKE clause works fine, but could not make it for the select statement. Sometimes (for eg. in my case) there are over hundred of fields. Would not like to get them all the time…

  24. Hi. Is there a way to concatenate a parameter with a symbol to pass for LIKE statements? Here’s an example:

    I have 2 parameters department and department% which has 1 and 1% respectively. second parameter is for LIKE statements but this means end user has to select 2 values when they try to load the report. Any ideas how i can concatenate % in sql to avoid the second parameter?

  25. Hi Chris, Thanks for your blog. I would need my SQL (complex enough to accomplish with only modeling in PBI) to be scoped based on user selected slicer values. So it should trigger the query, pass the parameters on run time and show the results back in Power BI. Can you suggest the best possible approach to acheive this. Thank You.

  26. Pingback: Reducing No of Meta Data Queries send from Power BI to Synapse – Asanka @ ShareWhatIKnow

  27. Hi Chris,

    I’m trying create a PBI report at PBI Service that allows input of 3 parameters to run a SQL spatial query, it is possible ?

    I need to get from user input Easting, Northing and Buffer like below.

    Thanks in advance

    DECLARE @p geometry;
    DECLARE @p1 geometry;

    SET @p1 = geometry::STGeomFromText(‘POINT(1844577 5812538)’, 2193).STBuffer(2000)

  28. HI Chris, would it be possible to drop an existing table and create another one in SQL database through “Value.NativeQuery”?

Leave a Reply to Cool Blue Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: