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 

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    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,

     

    • WorkWithCode's avatar
      WorkWithCode
      Frequent Visitor
      6 years ago

      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's avatar
        v-lid-msft
        Community Support
        6 years ago

        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,