Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
50 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |