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 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

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,

     

7 Replies

  • 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,