cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Hi Everyone @ptrk  @GURUPRASADB  @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.

 

 

 

Highlighted
Frequent Visitor

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

Hi @alirazamir83.

 

I took a quick look at your query and the problem is definitely on your second one.

Try breaking it like this and let me know if it worked:

 

"...

data= Json.Document(Web.Contents(https://openapi.example.com/,
[
RelativePath="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,
Headers = [
#"Authorization"="Bearer "&token,
#"Content-Type"="application/json",
#"apiKey"="845RCEJTL63884jlkjaellT77kwnn"
]
])),

..."

 

The bottom idea with this issue is the the Power Bi service doesn't want anything dynamic on the main URL, that's why everything with parameters should go on the Relative Path or the Query sections.

You should always make a call to Web.Contents with only the domain name as the first parameter and then break the rest of the URL within the others parameters of the function.

 

Hope it helps!

Highlighted
New Member

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

(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

Highlighted
Helper I
Helper I

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

Good morning, I am having the same problem but when connecting to some DBF tables from different databases concatenated by a GetData, I understand that I would have to encode the DataSource in some way so that the power bi service recognizes it, but I do not understand how do it, any kind of help would be appreciated, I share the code of GetData and the one of the resulting table, thank you very much

 

(Nlocal)=>
let
    Origen = Excel.Workbook(File.Contents("C:\Users\administrador.ESTANCIAS\Documents\Power BI Desktop\Sucursales.xlsx"), null, true),
    Sheet1_Sheet = Origen{[Item="Sheet1",Kind="Sheet"]}[Data],
    LOCAL = Sheet1_Sheet{Nlocal}[Column9],
    

    Source = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\LINCEV3\"&LOCAL&"\DBF\;extended properties=dBASE IV",
    [Query="select CLCOD, CLTPO, CLNOM, CLDIR, CLLOC, CLCP, CLCUIT, CLVCODPAG, CLFECHA, CLFING, CLEMAIL from [cli.dbf]"])
in
    Source
let
    Source = Excel.Workbook (File.Contents ("C: \ Users \ administrator.STANCES \ Documents \ Power BI Desktop \ Branches.xlsx"), null, true),
    Sheet1_Sheet = Origin {[Item = "Sheet1", Kind = "Sheet"]} [Data],
    # "Promoted Headers1" = Table.PromoteHeaders (Sheet1_Sheet, [PromoteAllScalars = true]),
    # "Type changed" = Table.TransformColumnTypes (# "Promoted headers1", {{"LOCATION", type text}, {"GROUPING", type any}, {"LOCAL_NAME", type any}, {"LOCAL_NAME_TOTAL", type any}, {"COD_LOCAL", Int64.Type}, {"CONCEPT", type text}, {"SSS", type text}, {"TYPE", type text}, {"LOCAL", type text}, { "Active", Int64.Type}, {"Order", Int64.Type}}),
    # "Custom added" = Table.AddColumn (# "Type changed", "Custom", each GetDataCli ([COD_LOCAL])),
    # "Other columns removed" = Table.SelectColumns (# "Custom added", {"COD_LOCAL", "Custom"}),
    # "Custom expanded" = Table.ExpandTableColumn (# "Other columns removed", "Custom", {"CLCOD", "CLTPO", "CLNOM", "CLDIR", "CLLOC", "CLCP", "CLCUIT" , "CLVCODPAG", "CLFECHA", "CLFING", "CLEMAIL"}, {"CLCOD", "CLTPO", "CLNOM", "CLDIR", "CLLOC", "CLCUIT", "CLVCODPAG" , "CLFECHA", "CLFING", "CLEMAIL"}),
    # "Duplicates removed" = Table.Distinct (# "Custom expanded", {"CLCOD"})
in
    # "Duplicates removed"

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.