Cannot Schedule refresh Azure SQL Database

[DaxNovice]

Hi

I am currently getting the error message below when trying to refresh from the service (works fine on desktop):

You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

(List of Queries, all from SQL Database)

Query contains unsupported function. Function name: Sql.Database

From the refresh history the error message is :

Data source error: Unable to refresh the model () because it references an unsupported data source

The purpose of the function is to append together mulitple databases & servers that have the same structure into one single table.  e.g. advanced editor below:

let
Source = #"DB & Server",
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Server", type text}, {"DB", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "pbig MP", each #"pbig MP"([DB], [Server])),
#"Expanded Query1" = Table.ExpandTableColumn(#"Invoked Custom Function", "pbig MP", {"Name", "Data", "Schema", "Item", "Kind"}, {"Name", "Data", "Schema", "Item", "Kind"})
in
#"Expanded Query1"

The Source "Db & Server" has two columns, DB & Server, and 4 rows with unique Db & server combinations.

Has anyone had this issue before & managed to solve?

advanced editor text of the function is:

(DB as text, Serv as text) as table =>
let
Source = Sql.Database(Serv, DB)
in
Source

[venal]

@DaxNovice 

I tried to re-pro the issue with On-Prem SQL Server from my end by connecting to a DEPT table.

let
Source = Sql.Database("DESKTOP", "Venal"),
dbo_DEPT = Source{[Schema="dbo",Item="DEPT"]}[Data]
in
dbo_DEPT

Created a sample report and published.

Tried to check with "refresh Now", it got completed.

If the issue is persists, please share the error details.

If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

[DaxNovice]

Hi @venal 

Thanks.  Yes, it works with static Sql.Database("Server name","Database name") but not with parameterized server & DB which is the issue.

from this link here

Note

Power BI currently does not support parameterized data source definitions, also known as dynamic data sources. For example, you can't parameterize the data access function Sql.Database("SqlServer01", "AdventureWorks"). If your dataset relies on dynamic data sources, Power BI informs you that it detected unknown or unsupported data sources. You must replace the parameters in your data access functions with static values if you want Power BI to be able to identify and connect to the data sources. For more information, see Troubleshooting unsupported data source for refresh.

But clicking that link gives you no further info on how to fix other than static server name & database which is not a viable option for this dataset.

I understand this could be a bit of a niche problem but if anyone has had this and has a solution I would be most grateful

Error message here if this helps:

Something went wrong
There was an error when processing the data in the dataset.
Please try again later or contact support. If you contact support, please provide these details.
Data source error: Unable to refresh the model (id=7923608) because it references an unsupported data source.
Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID: ff09dc5c-42df-458a-97c7-7b0a060af6bc
Request ID: 0e77e53b-7597-d328-b8dc-7363438cb002
Time: 2019-11-13 06:08:02Z

[v-lili6-msft]

HI @DaxNovice 

Please refer to this document:

Troubleshooting unsupported data source for refresh

Regards,

Lin

[DaxNovice]

HI Lin

I am not sure that helps.  The source is SQL.Database, which is a supported source for data refresh.  This is also an Azure SQL database so there is no need for a gateway.

I gather that this is probably due to the functions but there is no documentation that I can find to help with this.

Refresh works find on the .pbix file