Power Query parameters to change source settings

[redwards59]

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

[theov]

This will help on thi subject. For creating a parameter from Orcale source or using a parameter in Orcale sql query.

https://www.youtube.com/watch?v=Qk9yLKtl8t8

[redwards59]

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]

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-b...

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.

[redwards59]

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