Dataset contains a dynamic data source

[Fleur123456]

I have several queries in Power BI Desktop. Everything works fine. Now I publish my report in Power BI Service and I get the error message as mentioned in the title. How can I adjust my code so that it also works in Power BI Service? I do the following:

Query1 (disabled from load; basically returning an empty table if sql database server/name are empty) 

let
// Define server and database names directly as static strings
ServerName = "", // Use a fixed server name
DatabaseName = "", // Use a fixed database name

// Connect to the MySQL database with static server and database names
Source = MySQL.Database(ServerName, DatabaseName, [ReturnSingleDatabase=true]),

// Attempt to retrieve data from the specific table within the schema
forecast = try Source{[Schema="", Item="forecast"]}[Data] otherwise
// Return an empty table if retrieval fails
Table.TransformColumnTypes(
#table(
{""},
{}
),

)
in
forecast

Query2 (enabled from load)
starting from query 1

How can I change these queries, such that I do not encounter the issue in pbi service?

[v-echaithra]

Hi @Fleur123456 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @Fleur123456 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @Fleur123456 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @Fleur123456 ,

Thanks for the reply.

The core reason you're encountering this issue when publishing to Power BI Service is due to Power BI Service's strict limitations on dynamic data sources.
This is known as a dynamic data source, because the actual database being queried depends on a value evaluated at runtime.
In Power BI Desktop, this is allowed because everything runs locally and interactively. But in Power BI Service, it’s different, the Power BI Service pre-validates all data sources when publishing or refreshing.
In most cases, Power BI semantic models that use dynamic data sources can't be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed.

To determine whether your dynamic data source can be refreshed, open the Data source settings dialog in Power Query Editor, and then select Data sources in current file. In the window that appears, look for the warning message, as shown in the following image:

If that warning is present in the Data source settings dialog that appears, then a dynamic data source that can't be refreshed in the Power BI service is present.

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra E.

[Fleur123456]

Thanks for the explanation. We receive this warning indeed. Do you have any suggestions how to fix this?

Regards,

Fleur

[v-echaithra · Accepted solution]

Hi @Fleur123456 ,

Thank you for the update.

This error usually happens when you connect to a data source using a dynamically generated url.

Power BI blocks combining data from different sources with incompatible privacy levels for security reasons. When you dynamically construct a URL, Power BI cannot determine the privacy level of the resulting full URL, and it defaults to treating it as a combined source. If the root/base URL is set to a different privacy level than the dynamic part (e.g., parameter or other data source), it triggers a “Formula.Firewall” error or credential error.

Closing this dialog and going back to the Query Editor, if you click the Edit Credentials button, you can set credentials for the data source (anonymous access is fine in this case). For more information, please refer to: Chris Webb's BI Blog: Credentials, Data Privacy Settings And Data Sources In Power Query/Power BI

Please refer to these articles, might hold some useful tips: 

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra E.

[v-echaithra]

Hi @Fleur123456 ,

Even though you're not loading query1, Power BI still evaluates it during publishing and since query1 uses dynamic values in server name, database name, Power BI can't validate the source, and it triggers the dynamic data source error.
Try to replace "your-fixed-server" and "your-fixed-database" with your actual values , they must be static for Power BI Service.

Query 1

let
ServerName = "your-fixed-server", (Replace with your actual server)
DatabaseName = "your-fixed-database", (Replace with your actual database name)

Source = if Databasename <> "" then
try MySQL.Database(Servername, Databasename, [Returnsingledatabase=true])
otherwise #table({}, {})
else
#table({}, {}),
forecast = try Source{[Schema="", Item="forecast"]}[Data] otherwise #table({}, {})
in
forecast

Query 2

let
ResultfromQuery1 = Query1,
Finaldata = Table.Transformcolumns(ResultfromQuery1, {...})
in
Finaldata

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra E.

[Fleur123456]

Hi, thanks for your help! The solution still doesn't work, unfortunately 😞

[Fleur123456]

@lbendlin 
In some cases there is no sql database, then I leave the database and servername empty. I would expect the retrieval to fail and want the query to deliver an empty table.

[Fleur123456]

Okay, how would you do that if I do not have a server and databasename in the cases I would expect an empty table?

An alternative I see is using a boolean so that query 1 would look like:

if Boolean_Datebase then
let
Source = try MySQL.Database(ServerName, DatabaseName, [ReturnSingleDatabase=true]) otherwise
Table.TransformColumnTypes(#table( {""}, {} ), )
forecast = Source{[Schema=DatabaseName,Item="forecast"]}[Data]
in
forecast
else
let
EmptyTable = Table.TransformColumnTypes(#table( {""}, {} ), )
in EmptyTable

but I then still receive the same error

[lbendlin]

You must always specify a fixed server and database name in the original source query, no matter if you plan to use it or not.  You can then swap out the database name (assuming you have access to both databases)  for your query.  You can also get creative and use a linked server in your query etc.  But the initial names must be static.

[Fleur123456]

There is only 1 database. So in the first case I would like to retrieve the data belonging to this database with a certain databasename and databaseserver. In the second case, there is no database, I would like to retrieve an empty table. 

Currently, I try to retrieve the data/empty table in query 1 and disabled load. In query 2, I continue with the result from query 1 and do load this one; this gives the dynamic data source error.

[lbendlin]

you need to do that at a later stage. Server and Database name must be static and valid.

[lbendlin]

what would be the reason for the retrieval to fail?