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,

     

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,

       

      • WorkWithCode's avatar
        WorkWithCode
        Frequent Visitor
        6 years ago

        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.