Creating a generic data source for a PQ query

[Anonymous]

Hi,

for a customer I need to manage the data loading from a SQL Server table in the dev environment and the data loading from the corresponding Oracle table in the prod environment. In other terms, I've two different data sources between two environments.

I thought to create an empty query having as a first row something like Source = Connection_string_param, where this parameters contains the database server, the database name, likely also the access credentials and perhaps a table name, e.g.:

- for the SQL Server source, Connection_string_param = Sql.Database("database_server", "database_name"),

- for the Oracle source, Connection_string_param = Oracle.Database("database_name").

Is it possible to create a such parametric query? Any helps to me, please? Is it possible switching somehow from a data source to the another one?

Many thanks

[mahoneypat · Accepted solution]

Please see this article.  You should be able to adapt this approach.

Change the Source of Power BI Datasets Dynamically Using Power Query Parameters - RADACAD

Pat

[mahoneypat]

There are several ways you could do this.  One simple one would be like this.  Just change the SourceToggle value away from 1 to change to prod.

let

SourceToggle = 1,

Source = if SourceToggle = 1 then Sql.Database(     ) else Oracle.Database(   ),

rest of query

Pat

[Anonymous]

Hi,

I've SourceToggle as a PQ parameter, but after publishing the pbix on Power BI service I cannot edit this parameter.

How could I solve this issue? Thanks

[Anonymous]

Hi Pat,

thanks for your reply.

I've tried your suggest in pbi desktop and it functions rightly!

On power bi service after the publishing?

I'm interesting to know other ways to solve this issue. Is there any articles about this subject?