Forum Discussion
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
- lbendlin
Super User
what do you get when you use %% ?
- StarkPBI
Advocate 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 😑