Forum Discussion

TSantoro99's avatar
TSantoro99
New Member
5 years ago

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

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

 

 

 

Need Help

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    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.

  • TSantoro99's avatar
    TSantoro99
    New Member
    5 years ago

    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!