Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jdibala
Frequent Visitor

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

1 ACCEPTED SOLUTION
KNP
Super User
Super User

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

12 REPLIES 12
lbendlin
Super User
Super User

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

jdibala
Frequent Visitor

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
Super User
Super User

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
lbendlin
Super User
Super User

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

 

lbendlin
Super User
Super User

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.

 

 

 

 

When I remove brackets and semicolon it accepts the parameter name

jdibala_0-1622638865850.png

I need some kind of escaping

lbendlin
Super User
Super User

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?

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
Super User

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

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

 

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

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

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors