I've been running into a weird bug in Power Query as I try to create a function to retrieve data from a REST API and have it work with Power BI Services to automatically refresh said dataset.
My current code is as follows:
Function:
(Page as number) =>
let
Kilde = Json.Document
(Web.Contents
("https://<base url>/studentundervisning?",
[RelativePath= "",
Query =
[
fields=Uri.EscapeDataString(pfields),
dbId = "false",
refId = "false",
page = Number.ToText(Page),
limit="100",
undervisning.emne.institusjon="186",
undervisning.semester.ar = pÅr,
undervisning.semester.termin= Text.From(pTermin)
],
Headers=
[
#"X-Gravitee-Api-Key"="<key value>"
]
]
)
),
#"Konvertert til tabell" = Table.FromRecords({Kilde}),
#"Utvidet items" = Table.ExpandListColumn(#"Konvertert til tabell", "items")
in
#"Utvidet items"
Parameters:
pfields = * (and I have tried to use %2A)
page = 1
pÅr = 2023
ptermin = HØST
Expected value:
https://<base url>/studentundervisning?fields=%2A&dbId=false&refId=false&page=1&limit=10&undervisning.emne.institusjon=186&undervisning.semester.ar=2023&undervisning.semester.termin=H%C3%98ST&fremmote=true
Returned value:
DataSource.Error: Web.Contents kan ikke hente innhold fra https://<base url>/studentundervisning/?fields=%252A&dbId=false&refId=false&page=1&limit=100&undervisning.emne.institusjon=186&undervisning.semester.ar=2023&undervisning.semester.termin=H%C3%98ST (404): Not Found
Detaljer:
DataSourceKind=Web
DataSourcePath=https://<base url>/studentundervisning
Url=https://<base url>/studentundervisning/?fields=%252A&dbId=false&refId=false&page=1&limit=100&undervisning.emne.institusjon=186&undervisning.semester.ar=2023&undervisning.semester.termin=H%C3%98ST
Without fail Power BI double encodes the first part of my URL "fields=%2A" into fields=%252A. It either double encodes (when I add "%2A" as a value or try to use Uri.EscapeDataString), or won't encode at all if I just add pfields as a parameter.
It will then return the following URL:
https://<base url>/studentundervisning/?fields=*&dbId=false&refId=false&page=1&limit=100&undervisning.emne.institusjon=186&undervisning.semester.ar=2023&undervisning.semester.termin=H%C3%98ST
For some reason it does not double encode the last part of the query, undervisning.semester.termin= Text.From(pTermin), except if I post it as a Uri.EscapeDataString() value. Trying to use Text.from() in the first query part that gets double encoded does not help.
The only way to get around this weird bug is to manually construct the URL, but that also makes the Power BI Service unable to auto refresh the dataset to my knowledge. Besides, it takes a rather simple and elegant solution (thank you Curbal) and makes it a coding hack-job.
Does anyone know a way around this issue? I can't find anything on the net to fix this and most peoples guides work with simple APIs in their guides with fields=id,term,anothervalue etc. Not hex values.
I run a norwegian Power BI installation, but that shouldn't affect this issue to my knowledge.
