cancel
Showing results for 
Search instead for 
Did you mean: 
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

7 REPLIES 7
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.

frg88
Frequent Visitor

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?

frg88
Frequent Visitor

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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Solution Authors
Top Kudoed Authors