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.
We develop reports used by multiple clients. Each clients maintains a database with same schema. We have a database that holds information regarding the list of database for the clients. I want to pass in the databaseId through query paramenter from the report URL and the report should pick the right database using the databaseId and dynamically change the datasource. Is this feasible?
Thanks in advance.
Solved! Go to Solution.
Hi @aroyappan,
As @GilbertQ said, if you want to dynamic change the datasource, you can refer to below steps:
1. Create a table with these datasources.(id, server name, database name , item)
2. Write a power query custom funciton which used to analysis the records and connect to datasource.(function parameters: server name, user name, password)
3. Filter the datasource table and use the filtered records to inveke the custom funciton.
Sample of sql database
let loadData=(Servername as text, DbName as text, itemName as text)=> let Source = Sql.Databases(Servername){[Name=DbName]}[Data]{[Schema="dbo",Item=itemName]}[Data] in Source in loadData
Use:
Table structure: id, server name, db name, item name
let Selected = Table.SelectRows(SourceTable, each [id] = 10), ServerName= List.First(Selected[server name]), DBName= List.First(Selected[db name]), ItemName=List.First(Selected[item name]), Source= loadData(ServerName,DBName,ItemName) in Source
Regards,
Xiaoxin Sheng
Hi @aroyappan,
As @GilbertQ said, if you want to dynamic change the datasource, you can refer to below steps:
1. Create a table with these datasources.(id, server name, database name , item)
2. Write a power query custom funciton which used to analysis the records and connect to datasource.(function parameters: server name, user name, password)
3. Filter the datasource table and use the filtered records to inveke the custom funciton.
Sample of sql database
let loadData=(Servername as text, DbName as text, itemName as text)=> let Source = Sql.Databases(Servername){[Name=DbName]}[Data]{[Schema="dbo",Item=itemName]}[Data] in Source in loadData
Use:
Table structure: id, server name, db name, item name
let Selected = Table.SelectRows(SourceTable, each [id] = 10), ServerName= List.First(Selected[server name]), DBName= List.First(Selected[db name]), ItemName=List.First(Selected[item name]), Source= loadData(ServerName,DBName,ItemName) in Source
Regards,
Xiaoxin Sheng
Suppose we have multiple different SharePoint sites with same data schema and I want to query all of them dynamically based on say an initial query with the list of sites and merge the data. Is it possible ?
Thank you Xiaoxin.
How can i dynamically change data source after I publish the report?
Hi @aroyappan,
Currently, power bi service not support to use power query custom function, it only works on desktop side. For your requirement, you can post this to ideas.
Regards,
Xiaoxin Sheng
HI Xiaoxin
Does PowerBi support this feature now? I have similar requirement where I want to change data source , in my case , sql server connection string changes dynamically. DB schema , table does not change . Only sql server we want to connect to and Database changes. Please let me know . Thanks in advance.
Hi @aroyappan
Yes in theory that is correct.
You would have to have a list of the databases, as well as the returned data from your query.
After which you could then get the end result, which you could then assign to a parameter value, which in turn will then connect to the database.
You will have to do this in the Query Editor and possibly modify some of the M code to achieve this.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |