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:
[sourcecode language="text" padlinenumbers="true"]
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
[/sourcecode]
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:
[sourcecode language="text" padlinenumbers="true"]
Value.NativeQuery(
Source,
"SELECT * FROM DimDate
WHERE EnglishMonthName=?
AND
EnglishDayNameOfWeek=?",
{"March", "Tuesday"})
[/sourcecode]
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!
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
Yes, absolutely, you can pass in any kind of value including values from Power Query parameters.
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
usp_getTimeQeyDataPowerBI
(
@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.
Thanks,
Does this work with DirectQuery real-time dashboards?
No, it doesn’t – but interestingly it gives an error message rather than say that this doesn’t work… I need to research this more.
What is the preferred way to create real-time dashboards using power bi? Is it azure streaming analytics?
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, the query parameters could not be changed via slicers in the Power BI report. You could use Power BI parameters to send pass values to the query though.
Michal has a valid request. Will it be possible to choose a parameter from a slicer in a future release?
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.
Very Cool Chris. Like it!
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
Can you give me an example of the SQL you want to generate and the M code you have right now?
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
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
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.
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
Do you need to use your own custom SQL query? If not, this post shows how to do what you want and will generate a SQL query that uses an IN clause behind the scenes: https://blog.crossjoin.co.uk/2015/01/08/multiselect-filtering-and-functions-in-power-query/
Hi Chris
I used that method and it does not appear to use query folding so it brings in all product sales which is millions of records then filters the data, so ideally i want to be able to use a list from an excel file import and use the above method? is this straight forward?
thanks
Mike
That’s strange – folding should be taking place. Are you using it with your own custom SQL query though? That would prevent folding from taking place. Anyway, yes it should be possible, I’ll try to put together a proof of concept later today.
Also, using a join to do the filtering is also worth trying http://www.thebiccountant.com/2015/09/17/filter-sql-server-queries-with-excel-tables-query-folding-limitations-and-bug-workaround/
Yes i am using my own custom SQL query as it is cleaner because the database fields are poorly named etc. if you could come up with a solution that would be awesome and much appreciated. thank you.
Mike
You might find that it’s easier to not use a custom SQL query – whenever you do that, you stop all query folding taking place – and instead clean up the column names in Power Query. Alternatively you could create a view from your query, and then retrieve data from the view.
Hi Chris, Just to let you know i solved the problem using Text.Combine to replicate the list
thanks
Mike
Sorry – I didn’t have time to look at this. Glad you’ve found a solution!
Hi Mike, I am also stuck at the same point of passing a list to the IN clause. Could you share your approach. Thanks
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?
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 🙂
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.
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
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.
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
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
Hi Chris!
I’m trying to do something like this but instead get the values from step three, I want to get them from an URL, can this be done? I posted this on the Power BI Forum for more information. Hope you can help me.
https://community.powerbi.com/t5/Desktop/Get-value-from-C-app-to-Power-BI-Embedded/m-p/257508#M114797
Julián
great post, here’s another that complements this, it shows how to pass a parameter defined in power bi
http://funbiworld.com/2018-12-11-Parameters-power-bi/
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
I tried doing this with an ODBC datasource but got an error saying it’s not supported…
let
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 )
etc..
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):
let
Source = Sql.Database(Server, Database),
Query = Value.NativeQuery(
Source,
“SELECT
@rStart RangeStart
,@rEnd RangeEnd”,
[rStart=”2020-11-20″, rEnd=”2020-11-24″]
)
in
Query
THIS only works in Preview Mode
let
Source = Sql.Database(Server, Database),
Query = Value.NativeQuery(
Source,
“SELECT
@rStart RangeStart
,@rEnd RangeEnd”,
[rStart=@RangeStart, rEnd=@RangeEnd]
)
in
Query
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”.
Thanks,
Sean
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)
What is the difference between using Value.NativeQuery and Sql.Database when importing data?, wich is better?. Because I can write queries in both.
Value.NativeQuery has a few extra options but it’s not as easy to use as SqlDatabase. They should perform the same.
Thanks you Chris and excellent post
Is there a way to pass field names as parameters?
What do you mean by “field names”?
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…
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?
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.
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)
HI Chris, would it be possible to drop an existing table and create another one in SQL database through “Value.NativeQuery”?
No, making changes to a data source is not supported in Power BI or Power Query – you can only read data from a source.
Value.NativeQuery() works great to pass in query parameters, but is it possible to pass in parameter for example for table’s schema name? I wish to dynamically put together the SQL inside Value.NativeQuery(), where the table’s schema name is different from ‘dbo’.