Forum Discussion

jrieman's avatar
jrieman
Frequent Visitor
1 year 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
    1 year 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

     

     

jrieman's avatar
jrieman
Frequent Visitor
1 year ago

Running native queries I write.  Thank you for the links/info. 

 

 

 

  • lbendlin's avatar
    lbendlin
    Super User
    1 year 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
      1 year 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
        Super User
        1 year ago

        Your code is redundant - you could fold the SQL server into the Else branch.

         

        Does the refresh work on the Power BI Service?