Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Query contains unsupported function. Function name: Web.Contents for web API

Hi, I have following code I am using to get data from WEB API, I am getting error when I tried to schedule refresh in Powerbi Service. I am not sure what needs to be changed in code to get it work....
M Query
Web Contents failed
Anonymous's avatar
Anonymous
Not applicable
6 years ago

my_bi_quest Wow! Your post opened my eyes with the problem with refreshing dataset from Web.Contents! 🙂

 

Earlier I had:  

Web.Contents(BaseUrl, [RelativePath=RelativePath, Headers=Headers, Query=Query])

 

And the solution was only to change the first parameter to the string, not variable:

Web.Contents("https://xxxxxx.xxxxxx.net", [RelativePath=RelativePath, Headers=Headers, Query=Query])

 

Thank you very much!!! 😄 

osandrolucas's avatar
osandrolucas
Frequent Visitor
6 years ago

This solution does not work for me 
My function is like that:


(Param as text) =>

let
Source = Json.Document(Web.Contents(" MY URL " & ""&Param)),
#"Converted to Table" = ...,
#"Expanded Column1" = ...,
#"Renamed Columns"  = ...,
.

.

.

 

So, I use am list of parameter, concatenated with API URL. 

 

What is the sugestion for my case?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi osandrolucas 

    You still using full URL in one string.

    This is my function:

     

    (page as text) =>
  let
      RelativePath = "/Test/v3",
      Headers = [
        #"Subscription-Key" = SubscriptionKey
      ],

      Query = [
        from = Date.ToText(DateFrom, "dd/MM/yyyy"),
        to = Date.ToText(DateTo, "dd/MM/yyyy"),
        pageSize = Text.From(PageSize),
        page = Text.From(page)
      ],

      GetJson = (RelativePath, Headers, Query) => 
        let RawData = Web.Contents("https://xxxx.xxxxx.net", [RelativePath=RelativePath, Headers=Headers, Query=Query]),
          Json = Json.Document(RawData)
        in Json,

      PageResult = GetJson(RelativePath, Headers, Query)
  in PageResult

     

     

    When I will use "https://xxxx.xxxx.net/Test/v3" as a first parameter in Web.Contents it won't work 🙂

    • osandrolucas's avatar
      osandrolucas
      Frequent Visitor
      6 years ago

      Hi,
      Thanks for the support.
      I tried make like that, but, does not works.

      An example of call of my API is:

      http://folha-pagamento-facade-corporativo.agibank-prd.in/colaborador/demitidos/06-2020

       

      In this case, the parameter is 06-2020 (month - year) and I need make that in a lot of months years

       

      Following your suggestion, I have this error:

       

      I dont use Headers, because I dont have.

      (page as text) =>
  let
      RelativePath = "colaborador/demitidos",

      Query = [
        Parametro
      ],

      GetJson = (RelativePath, Query) => 
        let RawData = Web.Contents("http://folha-pagamento-facade-corporativo.agibank-prd.in", [RelativePath=RelativePath, Query=Query]),
          Json = Json.Document(RawData)
        in Json,

      PageResult = GetJson(RelativePath, Query)
  in PageResult

       

      Before that, my function was like that:

       

      (Parametro as text) => 

let
    Source = Json.Document(Web.Contents("http://folha-pagamento-facade-corporativo.agibank-prd.in/colaborador/demitidos/" & ""&Parametro)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"matricula", "nome", "dataDesligamento", "cpf", "localTrabalhoId", "motivoRescisao"}, {"Column1.matricula", "Column1.nome", "Column1.dataDesligamento", "Column1.cpf", "Column1.localTrabalhoId", "Column1.motivoRescisao"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.matricula", "matricula"}, {"Column1.nome", "nome"}, {"Column1.dataDesligamento", "dataDesligamento"}, {"Column1.cpf", "cpf"}, {"Column1.localTrabalhoId", "localTrabalhoId"}, {"Column1.motivoRescisao", "motivoRescisao"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"dataDesligamento", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "key_colaborador", each Text.From([matricula]) & Text.From([dataDesligamento]))
in
    #"Added Custom"

       

      But, this function returned that error: " Query contains unsupported function. Function name: Web.Contents " when I tried refresh automatic

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi osandrolucas 

        It looks that you should change your relative path.

         

        I see that parameter "06-2020" is a part of your relative path.

         

        So the proper relative path should be:

        RelativePath = "/colaborador/demitidos/06-2020",

         

        In your function I see that you don't need "Query" part.

         

        Query would be when you have additional parameters after "?" sign in your URL.

        For example I have:  https://xxxx.xxxx.net/Test/v3?page=2&pageSize=10000 (& all my parameters from the "Query")

        And you don't have that: http://folha-pagamento-facade-corporativo.agibank-prd.in/colaborador/demitidos/06-2020

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      (subscription_id as text) as table=> let // Get an access token using the Client Credentials AccessToken = Json.Document(Web.Contents("TOKEN_URL", [ Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"], Content=Text.ToBinary( "grant_type=client_credentials& client_id=XXX& client_secret=XXX& scope=XXX" ) ]))[access_token], body = "{ ""startDate"":""2010-01-01T00:00:00.000Z"", ""endDate"":""2030-12-01T00:00:00.000Z"", ""pageSize"":""100"" }", BuildQueryString = Uri.BuildQueryString(Parsed_JSON), // Call the target REST API, passing the access token as evidence of authorization Data = Json.Document(Web.Contents("URL/{subscriptionId}?subscriptionId=XXX", [ Headers=[Accept="application/json", #"Authorization"="Bearer " & AccessToken, #"Ocp-Apim-Subscription-Key"= "XXX", #"Content-Type"="application/x-www-form-urlencoded"], Content = Text.ToBinary(BuildQueryString), Query=[subscriptionId=subscription_id] ] )) in Data N.B: Don't forget to set the privacy level to Organisational in you data source settings otherwise refresh will fail from Power BI Service