Forum Discussion

jrieman's avatar
jrieman
Frequent Visitor
2 years ago
Solved

Is it Possible to Parameterize Server Type - Switch From MySQL to SQL Server?

edited for clarity Is it possible to parameterize  the database type I'm connecting to?  I want to change from MySQL server to SQL Server.   My reports query databases hosted on a MySQL server.  M...
Need Help
  • jrieman's avatar
    jrieman
    2 years ago

    I ended up doing it by making Source conditional, based off value of parameter, within the Power Query advanced editor.  

    I'm using the approach below.

     

    let
    Source =if server= "xerxes" 
    then MySQL.Database("xerxes.corp.brillo.com", "brilloweb_api", [Query="select *#(lf)from products;", ReturnSingleDatabase=true])
    else if 
    server = "alexander"
    then Sql.Database("alexander.corp.brillo.com", "brilloweb_api", [Query="SELECT * FROM products p;"])
    else MySQL.Database("xerxes.corp.brillo.com", "brilloweb_api", [Query="select *#(lf)from products;", ReturnSingleDatabase=true])  
in
    Source

     

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

Ok here's my proposal:

 

in your Power Query script have both the SQL and the MySQL Sources.  Make the query dynamic so that the "inactive"  connection runs something like "Select top 0 ... ", and the "active"  connection runs your actual query.  Append both queries.

jrieman's avatar
jrieman
Frequent Visitor
2 years ago

I ended up doing it by making Source conditional, based off value of parameter, within the Power Query advanced editor.  

I'm using the approach below.

 

let
    Source =if server= "xerxes" 
    then MySQL.Database("xerxes.corp.brillo.com", "brilloweb_api", [Query="select *#(lf)from products;", ReturnSingleDatabase=true])
    else if 
    server = "alexander"
    then Sql.Database("alexander.corp.brillo.com", "brilloweb_api", [Query="SELECT * FROM products p;"])
    else MySQL.Database("xerxes.corp.brillo.com", "brilloweb_api", [Query="select *#(lf)from products;", ReturnSingleDatabase=true])  
in
    Source