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

        Hi Everyone Anonymous  Anonymous  osandrolucas  kleroy my_bi_quest @

         

        I having the Same issue with the Oauth REST API. It is perfectly working in the Powe Bi desktop and refreshes as well. But when i Publish to the Bi server, A dataset is generated Automaticall and generating an error. The error is listed below.

        "You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

        • Data source for Query1

         Discover Data Sources

        Query contains unsupported function. Function name: Web.Contents "

         

        My M Query is:

         

        let
    token_url = "https://api.example.com/v2/oauth/token",    

    //This is for Dynamic Date filter of last 2 day which i have used in api base url
    endDate = Date.From( DateTime.LocalNow() ),
    startDate = Date.AddDays( endDate , -2 ),
    startDateText = Date.ToText( startDate, "yyyy-MM-ddT01" ),
    endDateText = Date.ToText( endDate, "yyyy-MM-ddT23" ),
    api_base_url = "https://openapi.example.com/api/reporting-details/v2/prod/views/PurchaseOrders?realm=myrealm&filters=%7B%22createdDateFrom%22%3A%22"& startDateText &"%3A00%3A00Z%22%2C%22createdDateTo%22%3A%22"&endDateText&"%3A59%3A59Z%22%7D",
    qry_str = "?myparameter",

// Getting Oauth Access Token
   body="grant_type=openapi_2lo",
   Source  = Json.Document(Web.Contents(token_url,
   [ 
     Headers = [
       #"Content-Type"="application/x-www-form-urlencoded",
        #"Authorization"="Basic Tyuek938nrkdtoo439nrhyeojgwpddam2ie6gsd8wokf08ugdiwjjtAOp73hwusb"
       ],
     Content=Text.ToBinary(body)
   ])),
    token = Source[access_token],
// Oauth Token Section Ended


    // Getting Data From Api 
    data= Json.Document(Web.Contents(api_base_url&qry_str,
   [ 
     Headers = [
                #"Authorization"="Bearer "&token,
                #"Content-Type"="application/json",
                #"apiKey"="845RCEJTL63884jlkjaellT77kwnn"
                ]
     
   ])),
    Records = data[Records],
    #"Converted to Table" = Table.FromList(Records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    //Alot of lines removed , Conversion to tabular form.
    in
    #"Renamed Columns"

         

        I have also replaced the url varibales with a string but i don't know what exactly the problem is. I am tired of this problem.

        Any help will be really appreciated.

         

        Thanks in advance.

         

         

         

  • 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