Web.Contents with complex Query parameter names

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

Need Help

KNP

5 years ago

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,
        ]
    ])

12 Replies

KNP

Super User

5 years ago

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,
        ]
    ])

jdibala
Frequent Visitor
5 years ago
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!
lbendlin
Super User
5 years ago
You need to use RangeStart and RangeEnd instead of from_dt and to_dt.
- jdibala
  Frequent Visitor
  5 years ago
  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
    New Member
    3 years ago
    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
lbendlin
Super User
5 years ago
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
  Frequent Visitor
  5 years ago
  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
Super User
5 years ago
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 In Power Query/Power BI Chris Webb's BI Blog (crossjoin.co.uk)

This may or may not impact you.
- jdibala
  Frequent Visitor
  5 years ago
  When I remove brackets and semicolon it accepts the parameter name
  I need some kind of escaping
lbendlin
Super User
5 years ago
sure. Most likely you only need to escape semicolon via %3B
lbendlin
Super User
5 years ago
Learn about partitions and the impact of meta data changes on the refresh scope.

Forum Discussion

Web.Contents with complex Query parameter names