Forum Discussion

StarkPBI's avatar
StarkPBI
Icon for Advocate I rankAdvocate I
5 years ago

Query paramter that includes the % symbol in web.contents not working

I'm trying to make a call to an API that will refresh in the PBI service. 

I have successfully gotten this to work on the desktop but now I'm trying to do the URL trick to get past the "This dataset includes a dynamic data source" error that Chris Web covered here. 

Here's my code

 

(x as text) as table =>
let
    Source = Json.Document(Web.Contents("https://api.up.com.au/?page%5Bafter%5D=XXXXXXX&page%5Bsize%5D=20", 
        [
            RelativePath="api/v1/transactions",
            Query=[#"page%5Bafter%5D"=x],
            Headers=[Authorization="Bearer up:yeah:MYKEY"]
        ])
    ),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "id", "attributes", "relationships", "links"}, {"Column1.type", "Column1.id", "Column1.attributes", "Column1.relationships", "Column1.links"}),
    #"Expanded Column1.attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.attributes", {"status", "rawText", "description", "message", "holdInfo", "roundUp", "cashback", "amount", "foreignAmount", "settledAt", "createdAt"}, {"Column1.attributes.status", "Column1.attributes.rawText", "Column1.attributes.description", "Column1.attributes.message", "Column1.attributes.holdInfo", "Column1.attributes.roundUp", "Column1.attributes.cashback", "Column1.attributes.amount", "Column1.attributes.foreignAmount", "Column1.attributes.settledAt", "Column1.attributes.createdAt"})
in
    #"Expanded Column1.attributes"

 

 
Here's my issue. 

The parameter that I need to change dynamically through the Query part of the web.contents function includes % symbols that I just can't get working. 

When I pass through the string "test" in the above function, this is what I get.

 

As you can see, the % symbol has been changed to %25. 

I need power query to update that parameter exactly as I've written it, which should mean that it overwrites the base URL parameter(with the XXXXXXXX's) and then I can dynamically grab the API data which should then refresh in the service. 

This is the part I need help with

 

Query=[#"page%5Bafter%5D"=page],

 


What do I need to write in that spot for it to generate correctly and not decode the % symbol to be %25?


I've tried so many different special character escaping techniques but nothings working ;(

2 Replies

    • StarkPBI's avatar
      StarkPBI
      Icon for Advocate I rankAdvocate I

      It returns %25%25.

      It's something to do with URL decoding I think. But I can't for the life of me work it out. I can't even use functions in that area (or I don't know how to). 


      I thought maybe I could concatenate multiple strings to handle the '%' as a special character but it always threw up an expectation error 😑