Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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:
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.
Sorry - I guess there is a limit to the post length. It cut off most of my message.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.