Forum Discussion

redwards59's avatar
redwards59
Frequent Visitor
6 years ago

Power Query parameters to change source settings

I have a Power BI model where I have setup parameters of SourcePlatform(Oracle,SQL Server), ServerName, ServiceName(for Oracle), and DatabaseName. I have defined a query for SourceConnection as follows:

 

let

SourceConnection =
if SourcePlatform = "ORACLE"
then

let

Source = Oracle.Database("(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST="&ServerName&")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME="&ServiceName&")))", [HierarchicalNavigation=true])

in Source

else

let

Source = Sql.Database(ServerName,DatabaseName)
in
Source


in
SourceConnection

 

 

Need Help

4 Replies

  • redwards59's avatar
    redwards59
    Frequent Visitor
    6 years ago

    Sorry - I guess there is a limit to the post length. It cut off most of my message.

  • redwards59's avatar
    redwards59
    Frequent Visitor
    6 years ago

    Here is the rest of the post.

     

    I then use SourceConnection in my data queries as follows:

     

    let
    BIQL_TbCalendar =
    if SourcePlatform = "ORACLE"
    then

    let

    BIQL = SourceConnection{[Schema="BIQL"]}[Data],
    result = BIQL{[Name="TBCALENDAR"]}[Data]

    in result

    else

    let

    result = SourceConnection{[Schema="BIQL",Item="TbCalendar"]}[Data]

    in result

    in
    BIQL_TbCalendar

     

    This works fine when I switch from SQL Server to Oracle, but when I switch to a different servername on SQL Server other than the one setup originally, Power Query throws a cannot authrnticate with the current credentials. I can access the same server through SSMS with my Windows credentials so I am not sure why it shouldn't work here. 

     

    Any advice appreciated.

     

    Thanks

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      6 years ago

      Hi redwards59 

      I see the code uses the same parameters of "datebase name" and "server name" for the two connection-Oracle and SQL Server.

      Please ensure the parameters of "datebase name" and "server name" are set correctly for both connection.

       

      Make a test to check if changing server name of SQL Server works in Power BI:

      Don't use complex code as you provide above, just create parameter for SQL Server  and use it in the connection string or connecting window UI as guided below:

      https://www.red-gate.com/simple-talk/sql/bi/power-bi-introduction-working-with-parameters-in-power-bi-desktop-part-4/

       

      Please let me know if it works,

      or some other details, eg, the "privacy level"  under "data source setting"->"edit permission".

       

      Best Regards
      Maggie
      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.