Query Parameters for dynamic change of PostgreSQL TABLE NAME

[andvpp]

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

[Anonymous]

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]

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]

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