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.

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

  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”])

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

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

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

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

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

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

  9. 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?

  10. 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 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s