Forum Discussion

WorkWithCode's avatar
WorkWithCode
Frequent Visitor
6 years ago
Solved

Web-api stops my dataset from refresing

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...
Need Help
Show and Tell
Tutorial Requests
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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's avatar
WorkWithCode
Frequent Visitor
6 years ago

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