Dynamic Connection (PostgreSQL or SQL Server) with Power BI Gateway

[adznnn7b]

Hi guys,

I have a report template in Power BI that will be replicated to several clients, and the Data Warehouse of these clients may vary between PostgreSQL and SQL Server.

To avoid keeping two separate PBIX files (one exclusively for PostgreSQL and the other for SQL Server), I tried to create a dynamic connection using parameters and conditionals (if-else) in Power Query. The idea is that, based on a defined parameter, the query dynamically uses the correct connector: PostgreSQL.Database or Sql.Database.

In Power BI Desktop, this approach works correctly. However, when publishing to Power BI Online, the service does not correctly recognize the data source, displaying the message:

“Some data sources cannot be listed due to manually created queries.”

As a result, I can't configure the gateway or schedule automatic updates.

Has anyone ever faced this situation or know of a practical and functional solution for maintaining dynamic connections between PostgreSQL and SQL Server in a single PBIX file, which is compatible with scheduled updates in Power BI Online?

Thanks in advance for any tips or guidance!

[v-pnaroju-msft]

Hi adznnn7b,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thank you, @lbendlin , for your response.

Hi @adznnn7b,

We appreciate your query on the Microsoft Fabric Community Forum.

Please find attached the document link and a screenshot for your reference, which may assist in resolving the issue:

Data refresh in Power BI - Power BI | Microsoft Learn

As per my research, the message, "Some data sources may not be listed because of hand-authored queries," indicates that the service detects dynamic data source definitions, which it cannot process for scheduled refreshes.

The Power BI Service has certain limitations concerning dynamic data source connections, particularly when parameters or conditional logic are used to switch between different data sources. This restriction primarily arises due to security and infrastructure considerations, as dynamically changing data sources can create challenges for the service’s data refresh and gateway configuration processes.

As a workaround,consider using Power BI Dataflows to perform the Extract, Transform, Load (ETL) processes for both PostgreSQL and SQL Server data sources. Dataflows allow you to centralize and standardize data transformation logic. The reports can then connect to these dataflows as their data source, simplifying the management of data connections and refresh schedules.

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[lbendlin]

Create two connections.  Use a parameter to decide which of them should return data, and which should return an empty table. Create two gateway connections accordingly.  Set both connection to skip the connection test.

[adznnn7b]

This was one of the approaches I tested, but Power BI Online still treats the query as manual/dynamic, preventing the Gateway from being configured.

What I did was create two connections:

SQL Server using Sql.Database().
PostgreSQL using PostgreSQL.Database().

I then used a parameter to activate one and return an empty table in the other, avoiding simultaneous queries to both databases.

Despite this, Power BI Online still doesn't recognize the sources in the Gateway, displaying the message:
“Some data sources may not be listed because of hand-authored queries”

This seems to indicate that the simple fact of conditioning the choice of connection is already interpreted as a dynamic query, blocking automatic updating.

[lbendlin]

You can try Expression.Evaluate but it likely will also be treated that way.