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:

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

image

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.

39 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:

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

  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?
    thanks!

    • 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
    [mylist=Parameter1]

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

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

    select * from sales
    where
    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.
    thanks
    Mike

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

    let

    Source = Sql.Database(“SQL”, “DB”),
    Connection = Value.NativeQuery(Source,
    “SELECT
    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’
    FROM
    ORD_HEADER
    INNER JOIN SL_TRANSACTIONS ON OH_ORDER_NUMBER = ST_ORDER_NUMBER
    INNER JOIN SL_PL_NL_DETAIL ON ST_HEADER_KEY = DET_HEADER_KEY
    INNER JOIN SL_ACCOUNTS ON CUCODE = OH_ACCOUNT_DELIVERY
    LEFT OUTER JOIN STK_STOCK ON DET_STOCK_CODE = STKCODE

    WHERE
    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
    DET_STOCK_CODE
    ,STKNAME
    ,oh_user2”)
    in
    Connection

  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
    Mike

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

        Screenshot: https://oxcrx34285.i.lithium.com/t5/image/serverpage/image-id/27563i7987B1B7417747BB/image-dimensions/2000?v=1.0&px=-1

      • 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: https://community.powerbi.com/t5/Desktop/How-do-I-pass-parameters-to-my-SQL-statement/m-p/119671#M50561

    Thanks for taking time to answer my comments 🙂

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

    Thanks
    Raghu

  12. 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.
    Thanks
    Gosta

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

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

    Thanks
    raghu

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

    Thanks
    Gosta

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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