Use list of values as input to SQL query

[Anonymous]

Hi,

I have a list of databases that is generated from a SQL query:

let
    Source = Sql.Database(Server, "master", [Query="SELECT name FROM master.dbo.sysdatabases"])
in
    Source

The list is then set to use a slicer and a search bar

I would then like to have other SQL queries to run and use the database name I select in my first list as an input value but cant make it work.

I have created a query list based upon the list of databases (then name of the query list i dbQueryList) and have a parameter called database with the type: text, Suggested Values: Query and I have then pointed out the list that is called "dbQueryList"

Now I want to have one of the selected database names as an input and run query like this one:

select count(*) from custom_table where status <>'Error'

How can I use the selected value from my first list and then have the query result updated based upon the name of the database?

[JosefPrakljacic]

Hey @Anonymous ,

here is a helpful blog article. The principle is the same I would say.

https://www.biinsight.com/power-bi-desktop-query-parameters-part2-dynamic-data-masking-and-query-parameters/

If this post was helpful may I ask you to mark it as solution and give it a 'thumbs up'? This will also help others

Have a nice day!

BR,
Josef
Graz - Austria

[Anonymous]

Thanks for your reply. 

The have problems when I try to authenticate against the server when I try to replace the value of the database name and instead point towards the list of databases

The query looks like this:

let
    Source = Sql.Database(Server, "&dbQueryList&", [Query="select count (*),  datepart (day, completed) as day,datepart (hour, completed) as hr from ChannelMessageHistory#(lf)where completed >= getdate()-1#(lf)group by datepart (day, completed), datepart (hour, completed)--, method#(lf)order by datepart (day, completed), datepart (hour, completed)--, method#(lf)"])
in
    Source

It will then try to login to an database with the name of "&dbQueryList&" which off cource doesnt work. How do I write the query to reference back to the list?