Forum Discussion

Fraz707's avatar
Fraz707
Helper I
6 years ago

Can we pass a SQL query as parameter from the database itself?

Hi

I was exploring the parameter functionality in power bi and have a doubt that can we pass a SQL query which is stored in the database , so can we pass those queries directly from the database to power bi as a parameter?.

 

The scenario is I have a table in SQL server in which I have diffrent SQL queries and I have to use those query to get the data into power bi , so I have to copy the query everytime from the database and hardcode it into the power bi whenever I want to import data into power bi therefore instead of this I want to ask if there is any way that we can pass those queries residing in the database directly as a Parameter instead of hardcoding it every time?

 

Thanks.

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Yes you can.

    As an example, I've create a table with ID and Query columns, called QueryTable

     

     

     

     

    Create a new query as you would normally, and paste in a sql query

     

    Now change the [Query=""] statement to pull data from the table instead.

    = Sql.Database("ServerName", "DatabaseName", [Query=List.First(Table.Column(Table.SelectRows(QueryTable, each [ID] = 1), "Query"))])

     

    Now create a query for each statement. You'll need to filter based on the ID, but this will work for you

    • Fraz707's avatar
      Fraz707
      Helper I
      6 years ago

      Thanks Anonymous  for your fast reply, I followed the step you mentioned and ran into an error, can you tell how to resolve this please? The error occured after this step Query=List.First(Table.Column(Table.SelectRows(QueryTable, each [Query_ID] = "CD1"), "Query"))]. Attaching the screenshot of the error.