Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am trying to add a custom column and Power Query seems to be swapping a , for %2C. My m code is as follows:
= Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents("https://api.os.uk/search/names/v1/nearest?point=" & [Easting] & "," & [Northing] & "&key=MY KEY", [ApiKeyName="test"])))
Unfortunately this appears to be failing as the highlighted comma appears to be being replaced. The subsequent error states:
DataSource.Error: Web.Contents failed to get contents from 'https://api.os.uk/search/names/v1/nearest?point=440200%2C449300&key=MY KEY'
Is there anyway to prevent this happening? Thanks
Solved! Go to Solution.
i'm now further confused as if i simply google the url as is shown in the error, the page loads without issue, seemingly accepting the switch to
'https://api.os.uk/search/names/v1/nearest?point=440200%2C449300&radius=1000&key=MYKEY'
why would this fail when used within an add column step? full error:
DataSource.Error: Web.Contents failed to get contents from 'https://api.os.uk/search/names/v1/nearest?point=440200%2C449300&radius=1000&key=MY KEY' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.os.uk/search/names/v1/nearest
Url=https://api.os.uk/search/names/v1/nearest?point=440200%2C449300&radius=1000&key=MY KEY
Please read the documentation. Then implement RelativePath and Query. point, radius and key need to go into the Query part.
like this?
= Table.AddColumn(#"Inserted Merged Column", "Custom", each
let x = [Easting] & "," & [Northing] in
Json.Document(Web.Contents("https://api.os.uk/search/names/v1/", [RelativePath = "nearest", Query = [point = x, radius = "1000", key = "MY KEY"], ApiKeyName="test"])))
unfortunately it's still giving me a bad request error.
= Table.AddColumn(#"Inserted Merged Column", "Custom", each
let x = [Easting] & "," & [Northing] in
Json.Document(Web.Contents("https://api.os.uk/search/names/v1/", [RelativePath = "nearest", Query = [point = x, radius = 1000, key = "MY KEY"]])))
try this
still no luck unfortunately, inital error gave me:
Expression.Error: We cannot convert the value 1000 to type Text.
i then added some "" around the 1000 upon which point i was prompted to enter credentials, went to 'Web API', entered my key and was met with;
A web API key can only be specified when a web API key name is provided.
so i added , ApiKeyName="test"] back in leaving me with:
= Table.AddColumn(#"Inserted Merged Column", "Custom2", each
let x = [Easting] & "," & [Northing] in
Json.Document(Web.Contents("https://api.os.uk/search/names/v1/", [RelativePath = "nearest", Query = [point = x, radius = "1000", key = "MY KEY"], ApiKeyName="test"])))
this ended in another bad request error unfortunately:
DataSource.Error: Web.Contents failed to get contents from 'https://api.os.uk/search/names/v1/nearest?point=440200%2C449300&radius=1000&key=MY KEY' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.os.uk/search/names/v1/nearest
Url=https://api.os.uk/search/names/v1/nearest?point=440200%2C449300&radius=1000&key=MY KEY
i am unsure if the comma is the real problem as pasting the url from this error into chrome works without a problem, it only seems to fail when coming from power query
Try with APIKeyName="key"
that's it! thank you so much for persevering with me on this. full solution:
= Table.AddColumn(#"Inserted Merged Column", "Custom2", each
let x = [Easting] & "," & [Northing] in
Json.Document(Web.Contents("https://api.os.uk/search/names/v1/", [RelativePath = "nearest", Query = [point = x, radius = "1000", key = "MY KEY"], ApiKeyName="key"])))
i wrongly assumed the key name element was some nominal value i was naming like a variable
Please read the documentation. Then implement RelativePath and Query.
Thank you for the response, I have updated things but am still facing an error. I have tried the following:
= Table.AddColumn(#"Reordered Columns", "Custom", each
let x = [Easting] & "," & [Northing] & "&key=MY KEY" in
Json.Document(Web.Contents("https://api.os.uk/search/names/v1/", [RelativePath = "nearest?point=", Query = [x], ApiKeyName="test"])))
this gives me the error: Expression.Error: The field 'x' of the record wasn't found.
if i remove the square brackets around the x so it reads Query = x, i am prompted to enter credentials however they will not authenticate.
however I am then prompted to enter credentials and upon entering my key it will not authenticate my credentials. I feel I must be getting the 'Query' field wrong.
User | Count |
---|---|
92 | |
86 | |
66 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |