Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
frg88
Frequent Visitor

Dynamic Data Source - Refresh failed

Hi,


I have a pbix with 1 SQL database query that obtains a group of SQL databases address (sql instance and database table).


After that, I invoke a function against the database list, as we can see in this tutorial: https://marcin.gminski.net/blog/dynamic-data-sources-in-powerbi-desktop/


When I try to scheduled the refresh into Power BI service, I obtain the error

You cannot schedule refresh for this dataset because it gets data from sources that currently don’t support refresh.


Is there anyway to use the scheduled refresh with this dynamic source?


Thanks in advance!

Fran

8 REPLIES 8
HiraRaffia
Frequent Visitor

I am having same issue. Were you able to find some work around ? 

v-xulin-mstf
Community Support
Community Support

Hi @frg88,

 

Power BI datasets that use dynamic data sources cannot be refreshed in the Power BI service. You can use the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed.

Please refer: Setting a scheduled refresh on a Dynamic Data Source in Power BI

Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code

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

 

Best Regards

Link

Thanks @v-xulin-mstf,

 

The problem is that I don't have a URL list, but I have a set of sql databases access.

 

Fran

Hi @frg88,

 

Maybe you can try this solution to  construct urls.

 

Best Regards,

Link

lbendlin
Super User
Super User

Maintain the list of databases as a static table directly in Power Query ( via Enter Data ) and then reference that in your next transforms.

Hi @lbendlin,


I have tried what you say but still have problems.


I have created a static table as you say, where I have the list of the 91 databases (server name and database name in each row). Then I have the following reference to the steps of a new table:

 

let
Source = Table.SelectColumns(#"BBDD", {"Server", "BBDD"}),
#"Query" = Table.AddColumn(Source,"Data",each Sql.Database([Server], [BBDD], [Query="select * from XXXXX"])),
#"Expand" = Table.ExpandTableColumn(#"Query", "Data", {"Data1", "Data2"})
in
#"Expand"


However, I still got the error message on the scheduled update in the Power BI service.


Thanks!

Fran

lbendlin
Super User
Super User

There are a couple of articles that discuss workarounds for this issue. Mostly revolving around creating a fake static data source so that the service is happy, and then at runtime swapping in the actual query parameters you want to run.  Might be tricky in your scenario though.

 

Question: how often does your list of databases change, realistically?

Realistically, the list of databases change once upon at year. However, the list contains 90 databases.

 

This was the only way I found to make the process "dynamic". Do you think there is a better way to do this?

 

Thanks!


Fran

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors