Data refresh not working : OK on desktop, NOK on Service - 'dynamic data sources'

[cdebackere]

I have a properly working powerbi in desktop that I want to move to the Service.

When trying to trigger a data refresh I get the error (freele translated from Dutch) 'This dataset contains a dynamic data source. As a result this data set will not be refreshed. See aka.ms/'

The dataset comes from a series of queries.

These queries are 

• generic queries functions : implementation of a SOAP API in custom query power m code. These rely for its data to come in by a call like: "Web.Contents(baseurl, [RelativePath=apiEndPoint, Content=cnt])"
• the actual data queries: calling the generic qury functions based on config data
• a calculated custom Date table
• tables which have a 'manually written' source. These contain configuration data for the web queries and the Date table calculation.  These use code like "Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZTbbsIwDIbfhe ..."
• parameters which contains very high level config for the called functions (like: use a boolean to define wether to use production or test URLs, the productionURL and the  testURL)

When check the settings as suggested I indeed see:

But what is causing this?

- the fact I have  manually entered these parameters of configuration tables?

- the fact that some test query's are set to 'do not load' or 'do not refresh'?

- ...

And what to do to prevent this error?

Thx for your advice,

Christof

[V-yubandi-msft]

Hi @cdebackere ,

If there is any post helps, please consider marking it as the Accepted Solution. This will help other members find the solution more quickly and efficiently.

Thank you for your cooperation.

[V-yubandi-msft]

Hello @cdebackere ,

Yes, you are correct. It is essential to ensure that the metadata remains stable so that Power Query treats it as a static source. One effective method to achieve this is by storing your configuration tables in a reliable location such as OneDrive, SharePoint, or a database, instead of manually entering them in Power Query.

For any future questions or discussions, I encourage you to stay active in the Microsoft Community.

Thank You.

[V-yubandi-msft]

Hi @cdebackere ,

To ensure efficiency and consistency, store configuration data in SharePoint or OneDrive rather than using manually entered tables. Predefine all expected columns in a  shell table to maintain consistent metadata across datasets. For Power BI Service, explicitly defined queries are necessary to enable scheduled refresh and ensure smooth operation.

Regards,

Yugandhar.

[cdebackere]

hi @V-yubandi-msft  and @lbendlin 

Thanks for helping me out. I found this a dificult topic to get my head around. I had difficulty understanding that that these are considered 'dynamic'.

So I now understand that hand-authored tables are no-go in BI Service. I must store these config tables in a onedrive/sharepoint/... excel file or so, which is accessible from the service. 

But you both talk about shells and shell tables. I seeams like this shell imposes a metadata frame assuring 'stability' of the tables. But I can't find information on that. When searching bing/google or on MS, the word 'shell' is pointing each time to powerShell. But that's not it. Where can I find info on that?

Regards,

Christof

[lbendlin]

You can call it whatever you want. You got the right idea - the meta data needs to be stable enough to fool Power Query into thinking that it is dealing with a static source.

[lbendlin]

You need to hide/mask your dynamic query parts.

Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1

And yes, queries that are set to "don't include in Refresh"  may still be included (ignoring your request) if they are part of the lineage.

[cdebackere]

Thanks for the link... but I don't see what I'm doing wrong using when defining:

baseurl = "https://X.Y.Z/soap/3/orga_test"

apiEndPoint = "getObjects"

cnt = Text.ToBinary("Some SOAP XML")

and then calling

Web.Contents(baseurl, [RelativePath=apiEndPoint, Content=cnt])

as far as I can read, I follow the rules, no?

Or does that imply I can not use 'manually written tables' inside a query to define a configuration? Because those are not loeded into the modelling, but are obviously linked by the query dependencies.

Forgive my ignorance.

[lbendlin]

Or does that imply I can not use 'manually written tables' inside a query to define a configuration?

Very likely but hard to tell without seeing the code.

[cdebackere · Accepted solution]

I eventually found the cause why I still got the dynamic source error:

in the code above I simplified the code as

baseurl = "https://X.Y.Z/soap/3/orga_test"

But in reality baseURL was calculated based on a boolean 'useTestURL', which then choose between 2 parameters: the prodURL and the testURL.

So, the baseURL used in the web.Contents was indeed dynamic. 

The solutions was: since test and prod have the same bare URI, I've split that part into a seperate parameter which is fixed. And the test/prod is now inserted are part of the relativePath.

baseurl = "https://X.Y.Z/soap/3/"

relPath = if useTest then testRelPath else prodRelPath

apiEndPoint = "getObjects"

cnt = Text.ToBinary("Some SOAP XML")

and then calling

Web.Contents(baseurl, [RelativePath=relPath & "/" & apiEndPoint, Content=cnt])

So stupid

[cdebackere]

maybe a screenshot of those 'config table' definitions which are manually written:

[lbendlin]

yes, if you expect to be able to run custom queries against these "tables"  (columns)  in your query - that will never work in the service.  Power Query does not allow a dynamic number of queries. At a minimum you need to have all possible queries present as shells. And even then - when these shells produce different results across refreshes (regarding number of columns , names and types) you will get another error that the meta data doesn't match.

[cdebackere]

Learning here: Shells. Never heard of, so I have some reading to do. Thanks for that 🙂

In the mean while, I understand your arguments about a service not liking that queries give 'dynamic results' in terms of 'query model' (number of queries, columns in the queries). 

1) number of queries: my query count does not change when modifying those config queries.

I have a function query 'getObjects(type)', I have a config table with a row for each type. And then I have the actual data queries written (pseudo) as rooms = getObjects("rooms"). So even when a column is added to the config table, no query gets added. One has to manually add an 'actual datavquery'. So there is nothing dynamic there for as far as I understand => I'm not in synch whith you ... what am I missing?

2) columns in queries: indeed, when changing something in a config table, it will automatically change the columns (add, remove, modify) of an actual data query. So that is indeed dynamic.

I have no problem if Service requires some kind of intervention (like reloading/...) because in this case (and same for case 1) the 'quey model' and datamodel changes, which is something that must happen under a change management process. A type 1 change is a major change and only happens rarely. A type 2 change a minor change and happens more frequently.

If understand well, using shells would 'wrap' the issue of a 1). Issue 2) would throw an error and need some intervention.

[cdebackere]

I replied with the code 2 times. The reply is there, and then when I check later the reply is gone. Something deletes my reply.

Is code not allowed?

[cdebackere]

the three config tables are 'Enter data' tables in power query.

This is one of them:

let
Bron = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZTbbsIwDIbfhespmrTHQbswqVvCnLhyHCTefiG0a9qlDGkSF/j85bfheDwMGFCAjDolPLz9shuu/9pg1V2d3ozeRjQB/DSHcADCoPdQPLNoCX2+HQ/C7E1I/oSS80aUyGEuFBPT6YJW83fLSeKjpSEOw+IahNPYCkRNXZ4ZUVfRmRksdOidNTeE+2ybRJxNlDxd6cMsZlu6iv5BO7GjB0c1/IaqoBQNpj47RGaEkrLB6rK+0XHIxvtPg4XFgh9TzK6o0PfrR6zFnXAsB81bM+e7+USdv/yrSVspKUUt+23jVglzuy+UoAi+COlCzybaM3aJStbE/hLjrOPT4M6pVGfbhFccWKbQhEl547zHuGXBIEyEnQHPKajh/udoX86q0FWgHJzg4Dy21dw+4pQcda689vGZ76J1/v90ju5CzTOY3pAVHcHWYu9VLhHX/e4X1+PXBUvIjMKDgM/7voI4CNoaUP/ka+Y+hfxnx2ErnOcOaV5Fvg4TyVmsO6+bbetzVdzJ/vwG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [rooms = _t, person.staff = _t, reservation = _t, course = _t, course.group = _t, course.studentset = _t, studentset = _t, curriculumlvl3 = _t, activity.type = _t, bookings.type = _t, legalentity = _t]),
#"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"rooms", type text}, {"person.staff", type text}, {"reservation", type text}})
in
#"Type gewijzigd"