Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
I am having same issue. Were you able to find some work around ?
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.