Forum Discussion

cdebackere's avatar
cdebackere
Icon for Resolver I rankResolver I
1 year ago
Solved

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

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 dyna...
Need Help
  • cdebackere's avatar
    cdebackere
    1 year ago

    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

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

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's avatar
cdebackere
Icon for Resolver I rankResolver I
1 year ago

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.