Passing date parameter to stored proc in query m

Power Query
1

Hi,

is it possible to pass date parameter to execute stored proc in query -m?

ex: exec dbo.dateparam @reportdate

please let me know the options i can explore. I tried below but throwing error:

[query="exec dbo.dateparam "&Selectdate ]

TIA.

2

@Ravi85 Yes you can. You just need to add quotation marks before SelectDate.

Steps for anyone else who will search for this later -

Create a Stored Procedure:

image
image1339×877 70.4 KB

Import data using a SP:

image
image699×556 15.1 KB

Go to Power Query and create Parameters.

image
image1005×556 31.2 KB

Edit the M code and add Punctuation/Quotation marks/Inverted comma.

image
image1649×480 40.4 KB

Result:

image
image1185×471 35.9 KB

6 Likes
3

Thanks @AntrikshSharma,

The users are looking at having slicer for date selection and any other options like having external table with slicer date options?

4

@Ravi85 What you are looking for is Dynamic M Query Parameters for Direct Query but as of today Direct Query doesn’t support Stored Procedure.

5

thanks @AntrikshSharma for heads up on direct query mode and what’s the possible solution for users to choose the date parameters.

I created stored procedure becz lot of calculations in select statements are based on parameter date and it’s bit difficult for me modelling/dax with its complexity.

TIA

6

Bumping this post for more visibility.

7

Hi @Ravi85 - If you are looking to pass Date parameter from a Slicer, as suggested by Antrisksh, can look into using Dynamic M parameters as explained in

As Stored Procedure are not supported in direct query, can copy entire select statement inside Power Query and apply Parameters there.

Hope this is helpful.

Thanks
Ankit J

8

thanks @ankit

As a quick win i have opted with Power query in excel as users are happy with that.(Opted this as no of refreshes per day limitations in power bi and also changing parameters in service is bit complex for end users).

9

Hi @Ravi85, did the response provided by @ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

10

That’s great @Ravi85 . Pls mark this post as closed if no other query.

11

One quick question @ankit even if we apply dynamic parameters do users always need to change them in service to refresh datasets? please Correct me if i’m wrong.