Web.Contents with complex Query parameter names

[jdibala]

Hi All

I've got a REST API which allows filter as a parameter. Everything works fine when I concat URL with parameters and variables in one string.

Source = Json.Document(Web.Contents("https://datasource.com/ItemHistory?filter(HistoryDate;GREATEREQUALS)="&from_dt&"&filter(HistoryDate;LESSTHAN)="&to_dt), 1252),

The problem is that query does not support incremental update as it is considered as dynamic data source. Microsoft says: "In most cases, Power BI datasets that use dynamic data sources cannot be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function."

So I tried to use RelativePath and Query in the code, but it seems it does not accept filter(HistoryDate;GREATEREQUALS) as a parameter name.

Source = Json.Document(Web.Contents("https://datasource.com"), 
    [
        RelativePath="ItemHistory",
        Query=
        [
            filter(HistoryDate;GREATEREQUALS)=from_dt,
            filter(HistoryDate;LESS)=to_dt,
        ]
    ])

Any chance to resolve this?

Thanks & Regards

Jan

[lbendlin]

Learn about partitions and the impact of meta data changes on the refresh scope.

[jdibala]

This seems to be working

Query=
        [
            #"filter(HistoryDate;GREATEREQUALS)"=from_dt,
            #"filter(HistoryDate;LESSTHAN)"=to_dt
        ]

#" " does the trick

Now I struggle with the incremental update itself.

@lbendlin @KNP  Thanks a lot for your support!

[KNP · Accepted solution]

Hi @jdibala, 

Does it work if they're quoted?

Source = Json.Document(Web.Contents("https://datasource.com"), 
    [
        RelativePath="ItemHistory",
        Query=
        [
            "filter(HistoryDate;GREATEREQUALS)"=from_dt,
            "filter(HistoryDate;LESS)"=to_dt,
        ]
    ])

[lbendlin]

sure. Most likely you only need to escape semicolon via %3B

[lbendlin]

maybe it is just a typo? Try this version:

Source = Json.Document(Web.Contents("https://datasource.com", 
    [
        RelativePath="ItemHistory",
        Query=
        [
            filter(HistoryDate;GREATEREQUALS)=from_dt,
            filter(HistoryDate;LESS)=to_dt
        ]
    ]))

But then I found this 

Chris Webb's BI Blog: Handling Multiple URL Query Parameters With The Same Name Using Web.Contents I...

This may or may not impact you.

[jdibala]

When I remove brackets and semicolon it accepts the parameter name

I need some kind of escaping

[lbendlin]

That sounds more like an issue with your data source (that URL parameter format looks iffy). Can you ask them for documentation and/or support?

[jdibala]

I've got documentation and everything works well with this code in PBI Desktop.

Source = Json.Document(Web.Contents("https://datasource.com/ItemHistory?filter(HistoryDate;GREATEREQUALS)="&from_dt&"&filter(HistoryDate;LESSTHAN)="&to_dt), 1252)

However, when I publish to the service it says that this data source is a dynamic type which is not possible to refresh. Therefore I am trying to reshape the code using RelativePath and Query.

[lbendlin]

You need to use RangeStart and RangeEnd instead of from_dt and to_dt.

[jdibala]

Hello, 

I know what you mean but this is actually a custom function which I invoke from query where RangeStart and RangeEnd is applied.

(from_dt as text, to_dt as text) =>

The problem I am facing is a syntax error on the below part where filter(HistoryDate;GREATEREQUALS) is not accepted as a parameter name.

        Query=
        [
            filter(HistoryDate;GREATEREQUALS)=from_dt,
            filter(HistoryDate;LESS)=to_dt,
        ]

[edwinvsgtMar]

here is my Web.contents code using queries "filter and select", it worked well for.

url = "https://api.MySite.com",

/*my variables - no URL encoding needed*/
Address = "555 Jones rd",
timestp = "2022-02-06T16:55:14-05:00",

/*Headers*/
headers = [#"Content-Type"="Applition/json",
#"Accept"="application/json, text/plain, */*",
#"Authorization"=Token,
#"Origin"="https://MySite.com",
#"Referer"="https://MySite.com/",
#"sec-ch-ua"= """Not_A Brand"";v=""99"", ""Google Chrome"";v=""109"", ""Chromium"";v=""109""",
#"sec-ch-ua-mobile"="?0",
#"sec-ch-ua-platform"="Windows",
#"Sec-Fetch-Dest"="empty",
#"Sec-Fetch-Mode"="cors",
#"Sec-Fetch-Site"="same-site",
#"User-Agent"="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/109.0.0.0 Safari/537.36"
],

/*Relative Path*/
RPath = "odata/Addresss",

/*Queries*/
query =[#"$select"="id,shipto,ReferenceId,customer,facilityId",
#"$filter"=" contains(ReferenceId, '"&Address&"')", /*Variable Contatenation is occuring on this line*/
#"$top"="15",
#"timestamp" = timestp
],

web = Json.Document(Web.Contents(url, [Headers=headers, RelativePath=RPath, Query=query] )),
value = web[value],

hope it helps

[JasonMc]

Thank you for this, it helped resolve my issue. I was getting 'invalid identifier' on the $ symbol. 

Using this format stopped the error.  #"$select"