End User ability to change SQL query parameters

[clqLoveSpace]

Hi All

One of the few things I liked about using Microsoft Query back in the day was the ability to put a ? in a query that could reference a cell value to accept a user-defined parameter into the SQL. This was really useful when creating reports where the end-user would need to be able to fine-tune a query without SQL knowledge or intervention from someone with SQL knowledge. Unfortunately this was not possible if you wanted to pull data through Power Query.

My question is, is it possible to set this up in Power BI Desktop (or not, since it's based on Power Query)?

E.g. I have a query that loops over a range of dates, increasing by 1 day per increment. Currently those dates are set to take the start of the previous and current month as the start and end points, but I want it to be possible for the end user viewing the report to define those dates, then refresh. Is this a feature or is this something that I will have to accept can only be done by changing the SQL?

[rashamohsen]

Hallo

I have always woundered why I can not do that, I thought it was something I didi not learn.
I would say it is a disadvantage of Power BI, it would help to has the ability to use parameters in the query and make them available for the end users.
Would you please let me know when you find the solution

Best Regards

[TomMartens]

Hey @clqLoveSpace 

to some extent this can be achieved using query parameters:

https://docs.microsoft.com/en-us/power-query/power-query-query-parameters

But you have to be aware that this will only work, when the user(s) has access to the Power BI desktop file, as soon as the pbix gets published there will be (and also should) be one dataset that is shared between your users. Whenever a user changes the parameter values, that data will change and all other users will be affected.

Hopefully this provides you with some additional ideas.

Regards,

Tom

[pawlowski6132]

Hi Tom, I would argue that I DO NOT want the same dataset for my users as different users have different authorizations to see different data. We should be able to define SQL parameters in a Direct Query (based on USERNAME()) which,  today we can't do as far as I know.

[clqLoveSpace]

Hi Tom

Thanks for responding - that's useful to know but it being only available for the end user to change the parameters via desktop isn't ideal, not fussed about the dataset being changed since it will only be used by the end user and our finance team who will both know to check the parameters on every refresh. The idea is we keep one for scheduled refresh with the current/previous month as described before and a separate one that they can alter to view historical data, only refreshing on demand.

However this makes it easier for our guys in finance to customize the parameters on behalf of our end users at least so it is still helpful to know.

[Mariusz]

Hi @clqLoveSpace 

Please refer to the video link below, where Patric explains how to achieve this.

https://www.youtube.com/watch?v=7TfV8qTUyfU

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.