Change parameter values within direct query sources

[martin_spaldin3]

Currently you can only change parameter values online within imported data sources, this would be useful to have on direct query and live connection data sources also as the ability to move through environments without redeploying is very useful for organisations.

[linda_sarace]

To expand upon this idea, we are looking to be able to modify the Direct Query parameters from PBI Service. Use Case: 1. We define parameters within the PBIX file in PBI Desktop on Direct Queries 2. A parameter that we might define will limit the data being sourced into PBI Desktop so that the PBIX can be more easily worked with in desktop (smaller data set) 3. We then, publish the PBIX file to PBI Service in Premium Capacity 4. We change the values in the parameters of the data set setting to expand the amount of data we are souring We currently can do this when using the Import Query, but it is not available when using Direct Query

[alex_rohr]

I have multiple locations that each have a Local DB with the same schema setup. Identical setups. I need to be able to change connection strings for each locations DB when in the Locations Workspace. Redeploying multiple times for each report or data change after changing ALL the direct query sources is just not feasible.

[sharada_boyanpa]

I vote for the idea.

[jim_manson]

Yes, this is needed

[SThornburg]

My use case is similar to the one posted on March 12, 2020. I have a PBI direct query dataset which has SQL Server instance and database names parameterized to allow easy migration between DEV, Test and Prod datasources. The report is deployed to a PBI Service shared capacity workspace. I would like to be able to update the parameters in the service under dataset settings. Currently my only option is to update the parameters in the desktop and republish.

[shkarnam]

As a workaround, we can use the Power BI Rest APIs to update the data source. In my case, I had a bunch of tables using the parameterized Azure SQL DB data source. Source = Sql.Database(AzSqlSrvName, AzSqlDbName) I had to convert one of the large table to use Direct Query mode. When I published the power bi desktop file to PBI Service and updated the parameters to point to the production SQL db, there were two data source connections established in the Power BI Service. Certainly, the connection string and the MQuery for the Direct Query mode table was cached. I used the Power BI Rest API and Powershell to update the data source for the Direct Query connection. Here are the steps taken: 1. Get DataSources for a given dataset using https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/getdatasources#code-try-0 The output returned was: { "@odata.context": "http://wabi-us-east-a-primary-redirect.analysis.windows.net/v1.0/myorg/$metadata#datasources", "value": [ { "datasourceType": "Sql", "connectionDetails": { "server": "myazureprod.database.windows.net", "database": "myPROD_db" }, "datasourceId": "44abc44a-4ab4-4cd4-444a-4acb4abc4444", "gatewayId": "11abc11a-1ab1-1cd1-111a-1acb1abc1111" }, { "datasourceType": "Sql", "connectionDetails": { "server": "myazureqasql.database.windows.net", "database": "myQA_db" }, "datasourceId": "00abc000-0000-000a-b000-c000aa00ee0", "gatewayId": "11abc11a-1ab1-1cd1-111a-1acb1abc1111" } ] } 2. Then iterate thru the list of Data Sources returned and determine the "datasourceId" that has a database name “myQA_db” 3. Then call Update Datasources API by passing the “datasourceId” and the new database name to “database” under “connectionDetails” node in the JSON request body. https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updatedatasources#code-try-0 Here is the JSON request body: { "updateDetails": [

[js7]

Please fix this ASAP.

[FrancoG1]

Surely obvious that this is needed.

[tatsiana_staras]

That feature is really needed. Otherwise, why the parameters, functions in power query now. All processes being switched to automatic. Please solve this problem.

[T186560V]

This is a real pain since I cannot distribute the template for data explorer. This is a huge limitation