Forum Discussion

andvpp's avatar
andvpp
Frequent Visitor
9 years ago

Query Parameters for dynamic change of PostgreSQL TABLE NAME

Hello,

 

In my company we use one database to store many tables.

All tables have the same structure, only the Table Name changes.

 

We want to create a Power BI template in order to visualize our data.

 

How can I create that keeping the Table as a dynamic parameter?

 

I have been able to change Databases using Query Parameters, following this example: http://biinsight.com/power-bi-desktop-query-parameters-part-1/   , but I don't know how to keep the same database and change the tables inside.

 

All the help that you can give me will be very useful.

Thank you very much!

Andrea

Need Help
Tips and Tricks
Tutorial Requests

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi andvpp,

     

    Based on my understanding, you want to use query parameter to dynamic modify the datasource and table, right?

     

    If this is a case, you can refer to below custom funciton formula and use the query parameter to invoke. 

     

    let
    loadTable=(ServerName as text,DataSource as text, SQLQuery as text, TableName as text) as table=>
    let
        Source = Sql.Database(ServerName, DataSource,[Query=SQLQuery])
        Table = Source{[Schema="dbo",Item=TableName]}[Data]
    in
        Table 
in
    loadTable

     Use sample:

    let
    Source= loadTable("xxxx", "xxxx", T-SQLquery,TableName)
in
    Source

     

     

    Regards,

    Xiaoxin Sheng

    • andvpp's avatar
      andvpp
      Frequent Visitor
      9 years ago

      Hello Xiaoxin Sheng,

       

      Thank you for replying!

       

      I am trying to use the function that you suggested. Here is a screenshot with the definition of the function in the Advanced Editor and the parameters I chose as an Input (Table "sales" in database "test_1"). I used a simple query to select all the columns in the table: "SELECT * FROM sales".


      When I try to call the function using these parameters, I get an error: " Error in query « Requête1 ». Expression.Error : The key doesn't correspond to any of the lines inside the table. 
      Détails :
          Key=Record
          Table=Table

       
      Do you know what this error message means?

      Andrea

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi andvpp,

         

        You can use the get data to connect to PostgreSQL database. After finish connect, open the "Query Editor" and "Advanced Editor" to copy the paramters in the formula. Then input them to the custom function to test again.

         

        In addition, I'm not recommend to use the "localhost" as the server name.

         

        Regards,

        Xiaoxin Sheng