Managing the switch between two different data source connections for a PQ query

[Anonymous]

Hi,
for a PQ query I need to using two different data sources that I manage in this way:

let
Source =
if Source_Switch = 1
then Oracle.Database(Database_Server, [HierarchicalNavigation=false])
else Sql.Database(Database_Server, Database),
...

where Source_Switch is a parameter for switching between the two data sources, Database_Server is a parameter

to specify the name of the database server and Database is a parameter to specify the name of the database.
For my query inside the desktop two connections are created despite the IF construct.
One of the these two connections is undesired because it is setted with the parameter values of the other one;

in other terms, the parameters are valid for only one connection.
In this situation, after the deployment on the Power BI service, one of the two related gateway connections goes in error.
So, or I need to remove one of the two created connections inside the desktop depending to the Source_Switch value

(but how? in the desktop it seems impossible to remove a data source connection) or I need to manage the above IF construct

with a function or I need to do another thing.
Any suggests to me, please? Many thanks

[edhans]

Can you create 3 queries for this?

1. Source connection to Oracle
2. Source connection to SQL
3. Your third query would be your parameter where you switch between using Query 1 or Query 2 as your source

let
Source =
if Source_Switch = 1
then SourceQuery1
else SourceQuery2

Make sure SourceQuery1 and SourceQuery2 are set to not load - right-click on each and uncheck Enable Load. Only query 3 will actually get loaded.

[Anonymous]

Hi, I've followed your suggests but I've not solved.

However two connections are created and not only one.

I could have an Oracle database in the prod environment and not a SQL Server database;

after the publishing, I need to manage the Oracle connection as a gateway connection,

while the SQL Server connection (that it could not be exist respect to the customer domain) refers to

a server named like the Oracle server (the parameters are used by the two data source connection).

Moreover, I've tried to put the connections inside a function, but in this way no connections exist.

If possible, the Source_Switch parameter should allow to manage only one connection based on

the value selection.

Thanks

[edhans]

You cannot dynamically create connections like that - the gateway config won't work. Parameters and data source configurations in things like Deployment Pipelines are designed to switch databases and servers, but not platforms. Cannot go from Excel files to CSV files, or SharePoint Lists to SQL, and you cannot go from SQL to Oracle. 

I understand your situation, but I've never seen an org run dev/test/prod on different platforms and expect to be able to seamlessly switch like that with any product.

Maybe someone will chime in, but the gateway is your kicker here. Even if it were cloud, it still wouldn't work. Say you had one datasource in Azure SQL and another in SharePoint Lists. You cannot maintain login credentials for an unavailable datasource hidden in your M code. If you activated it, your refresh would stop because the new source would need to be authenticated. And then the old source would vanish from the service's perspective, and if you switch back, you'd have to reauthenticate.

[Anonymous]

Hi edhans, thanks for your help!
The unique possible solution is to manage two distinct pbi models, one with the Oracle connection and the other one with the SQL Server connection.
I was doubtful to solve this issue in another manner.
Many thanks!