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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
TungNguyen_19
Helper III
Helper III

How to use only 1 parameter to switch different database connections rather than 2?

Hello everyone,

Based on many related helpful posts changing connections by using 2 parameters (just like this one ) but I faced to this task when I have theses Azure SQL databases with different URL and db-name:

NameStageServer URL
devDevelopmentdev.database.windows.net
uatTestuat.database.windows.net
prodProductionprod.database.windows.net

How to use only 1 parameter to changing datasource which corresponds to each stage of the development lifecycle?

Many thanks to you guys

1 ACCEPTED SOLUTION

Hi @TungNguyen_19 

 

When you connect to Azure SQL databases, Power Query uses Sql.Database function. It requires server and database as two separate parameters. So it seems not possible to use only one parameter to switch the data source. 

Sql.Database(server as text, database as text, optional options as nullable record) as table

 

If you use Odbc.DataSource, it may be possible to use only one parameter as it only requires connectionString. I haven't tested this yet. However ODBC connector only supports Import mode and the query performance is not as well as the SQL Server database connector. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

5 REPLIES 5
pratyashasamal
Super User
Super User

Hi @TungNguyen_19 ,
You can follow this link :-
https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters
Hope this is helpful.
Thanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Hi @pratyashasamal 

This is not the solution for my problem, I mean yes we can use parameter but in my problem we have separate different servers, different tables and I want to switch between them by only 1 parameter (that is the Stage parameter which ties pair server and table for each stage as I described in the table above). This is the idea and I don't know how to do it?

Thanks 

Hi @TungNguyen_19 

 

When you connect to Azure SQL databases, Power Query uses Sql.Database function. It requires server and database as two separate parameters. So it seems not possible to use only one parameter to switch the data source. 

Sql.Database(server as text, database as text, optional options as nullable record) as table

 

If you use Odbc.DataSource, it may be possible to use only one parameter as it only requires connectionString. I haven't tested this yet. However ODBC connector only supports Import mode and the query performance is not as well as the SQL Server database connector. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Hi @v-jingzhang 

One requirement arised that "Power BI deployment pipelines must NOT be used." Can you please explain what does this mean and does it related to this solution using parameter?

Many thanks for your support ❤️ 

Hi @TungNguyen_19 

I don't think it is related to the solution with parameter. May I know where you see the requirement?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.