Dynamic Query with function value

[torsten_FO]

Hi all,

in the past we have used a Query to a web.content. This query gets a value from a funktion (dynamic Start Date). This worked well until a few weeks ago. Here the Query:

= Json.Document(Web.Contents("http://www.myexample.com?P_WERK=[{'SIGN':'I','OPTION':'EQ', 'LOW':'CWMA'}]&P_DAT=[{'SIGN':'I','OPTION':'BT', 'LOW':'" & fktGetStartDate(10) & "','HIGH':'20211201'}]"))

Currently this is not possible (MS Documentation - dynamic data sources ).

How can I use a dynamic Date calculation in my Query?

Kind Regards,

Torsten 

[ibarrau · Accepted solution]

Hi. First of all, not all the scenarios of dynamic sources can be solved. Now, let's give it shot with this one. The most important thing with web content is the way the URL is built. We need to explain the engine that the source is only the first part of the URL in order to explain that all the requests are from the same source. Otherwise it thinks that each request is a different one and can't solve it.

Because of that you can use RelativePath and Query arguments to help you build the whole URL. Please check this example from the best, Chris Webb: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

It explains how to cut your URL and set the RelativePath and the Query for the arguments (Values after ? in links).

Once you change that, you will see if the dynamic query can be refreshed in Power Bi checking the data source settings. It should only have one item with the main URL.

I hope that helps,