Web-api stops my dataset from refresing

[WorkWithCode]

Hi all,

I have a problem with my dataset. I'm using a function in powerquery that pinpoints the exact location of someone's adress. But when I try to refresh the dataset I get this message: There was an error when processing the data in the dataset. 

So I started to investigate it a litte bit and when I got to the settings page I saw this message:  Query contains unsupported function. Function name: Web.Contents. So when I saw this I thought alright I just need to click on the function Skip connection on in the gateway but it didn't work. Can someone help me it is driving me nuts.

This is the function that is causing the problem:

= (location) =>

let
Bron = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/"[RelativePath="hello",Query=[q="SearchTerm"]
]&location&"?o=xml&key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")),
#"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
ResourceSets = #"Type gewijzigd"{0}[ResourceSets],
ResourceSet = ResourceSets{0}[ResourceSet],
#"Type gewijzigd1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}),
Resources = #"Type gewijzigd1"{0}[Resources],
Location = Resources{0}[Location],
#"Type gewijzigd2" = Table.TransformColumnTypes(Location,{{"Name", type text}, {"EntityType", type text}, {"Confidence", type text}, {"MatchCode", type text}}),
Address = #"Type gewijzigd2"{0}[Address]
in
Address

[v-lid-msft]

Hi @WorkWithCode ,

Does the query work when refresh in Power BI Desktop? We find there are some symbols missing in your query, We can try to change the query as following:

= (location) =>

let
Bron = Xml.Tables(Web.Contents("http://API_URL",[RelativePath="hello",Query=[q="SearchTerm "]
&location&"?o=xml&key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"])),
#"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"Copyright", type text}, {"BrandLogoUri", type text}, {"StatusCode", Int64.Type}, {"StatusDescription", type text}, {"AuthenticationResultCode", type text}, {"TraceId", type text}}),
ResourceSets = #"Type gewijzigd"{0}[ResourceSets],
ResourceSet = ResourceSets{0}[ResourceSet],
#"Type gewijzigd1" = Table.TransformColumnTypes(ResourceSet,{{"EstimatedTotal", Int64.Type}}),
Resources = #"Type gewijzigd1"{0}[Resources],
Location = Resources{0}[Location],
#"Type gewijzigd2" = Table.TransformColumnTypes(Location,{{"Name", type text}, {"EntityType", type text}, {"Confidence", type text}, {"MatchCode", type text}}),
Address = #"Type gewijzigd2"{0}[Address]
in
Address

Please try to post the request in other tools such as postman or other to verify if the parameter is valid, please hide your url of api if it contain any confidential information.

Best regards,

[WorkWithCode]

Hi @v-lid-msft 

First of all thanks for the help. I used your sugesstion and I think it works but know I get another error. 

An error occurred in the Findaddres query. Expression.Error: Cannot apply operator & to Record and Text types.
Details:
    Operator = &
    Left =
        q = Locations

Do you have an idea how I can fix this. Many thanks 

[v-lid-msft · Accepted solution]

Hi @WorkWithCode ,

Sorry for that it seems my previous formula is incorrect, please change the first step as following:

Bron = Web.Contents("http://API_URL", [RelativePath="hello", Query=[q="SearchTerm"&location,o="xml",key="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]]),

the value of Query option should be adjusted by your api, such as the location, we think it may be a url paramater, but if you want to q=SearchTermlocation, it may changed to following:

Bron = Web.Contents("http://API_URL", [RelativePath="hello", Query=[q="SearchTermlocation",o="xml",key="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]]),

Best regards,

[WorkWithCode]

HI, @v-lid-msft 

first of all thanks for all the help I think the issue around the URL is now been solved but I get 1 other error.

An error occurred in the Findaddres query. Expression.Error: Cannot convert a value of type Binary to type Table.
Details:
    Value = [Binary]
    Type = [Type]

I'm sorry if i'm asking too much but all of our datasets can't be refreshed.

thanks in advance 

[v-lid-msft]

Hi @WorkWithCode ,

Apologize, I am sure it is because my mistake, when I change the query, I forgot to add the Xml.Tables() Back after modify.

Bron = Xml.Tables(Web.Contens( Change to the query which works on your side...) )

Best regards,

[WorkWithCode]

hi, @v-lid-msft 

Sorry i found the solution myself. Thanks for all the help 

[WorkWithCode]

hi @v-lid-msft,

Thanks for the help I get only 1 last error message

Expression.Error: Access to the resource is prohibited.

 I thinks it's the api key but i'm not sure. I link the video where i have found this function here: https://www.youtube.com/watch?v=YxwU5UubWjI

thanks for the help.