Escape Special Characters '[' and ']' in API query

[TSantoro99]

I am attempting to setup an API query with relative paths so that it can be run from Power BI (web, not desktop). Unfortunately, the API I am working with uses '[' and ']' as part of its required parameter filtering. 😓

Does anyone know how to escape these characters so that the following bit of code would run and not throw an 'Invalid Identifier' error?

    Source = Json.Document(Web.Contents("https://secure.fleetio.com/api/v1/", 
    [
        Headers=[Authorization="Token token=""XXXXXXXXXX""", #"Account-Token"="XXXXXX"]
        RelativePath="service_entries"
        Query=[
            q[data_gt]="2019-09-01",
            q[s]="date+desc",
            page="" + page        ]    
    ])),

[TSantoro99]

Thank you for the responses. Phil, your layout seemed to work for querying within the desktop version, but unfortunately, it still was throwing an error when trying to setup an auto-refresh from the online PowerBi due to the dynamic nature of the query. (I think that is the wording they used....).

I think this will continue to be a brain-teaser for a while. 

Thanks for your help!

[PhilipTreacy]

Hi @TSantoro99 

In Power Query you can escape special characters in strings using their Unicode code e.g. LB = "#(005B)", and in a URL entered into a browser you could use %5B and %5D to replace [and ] but I cannot get this to work with Web.Contents.  Any attempt to use special characters, escape sequences or strings gives and error.

So you may have to just rebuild the query to allow for this, like so;

let 

    Param1 = "q[data_gt]=2019-09-01",
    Param2 = "&q[s]=date+desc",
    Param3 = "&page=" & page,

    FullRequest = "https://secure.fleetio.com/api/v1/?"&Param1&Param2&Param3,

    HTTPHeader = [RelativePath="service_entries",

                  Headers = [#"Authorization"="Token token=""XXXXXXXXXX""", #"Account-Token"="XXXXXX"]],

    Source = Json.Document(Web.Contents("https://secure.fleetio.com/api/v1/?"&Param1&Param2&Param3, HTTPHeader))

in
    Source

Please note that the value for page is not defined in that query.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[sevenhills]

Try and see if it works

     You can escape [  with %5B and ] with %5D

       based on https://www.w3schools.com/tags/ref_urlencode.asp     

also check the blog, to escape special characters 

https://blog.crossjoin.co.uk/2018/03/05/character-escape-sequences-in-m/