cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andvpp Frequent Visitor
Frequent Visitor

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

3 REPLIES 3
Community Support Team
Community Support Team

Re: Query Parameters for dynamic change of PostgreSQL TABLE NAME

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
andvpp Frequent Visitor
Frequent Visitor

Re: Query Parameters for dynamic change of PostgreSQL TABLE NAME

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".


Function_Definition.png

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

Community Support Team
Community Support Team

Re: Query Parameters for dynamic change of PostgreSQL TABLE NAME

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 260 members 2,959 guests
Please welcome our newest community members: