Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

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

4 REPLIES 4
edhans
Super User
Super User

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.