API url broken by Power Query

[Anonymous]

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

[Anonymous]

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

[lbendlin]

Please read the documentation.  Then implement RelativePath and Query.  point, radius and key need to go into the Query part.

Web.Contents - PowerQuery M | Microsoft Learn

[Anonymous]

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. 

[lbendlin]

= 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

[Anonymous]

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

[lbendlin · Accepted solution]

Try with APIKeyName="key"

[Anonymous]

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

[lbendlin]

Please read the documentation.  Then implement RelativePath and Query.

Web.Contents - PowerQuery M | Microsoft Learn

[Anonymous]

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.