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.

27 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

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