topic Re: Query contains unsupported function. Function name: Web.Contents for web API in Report Server

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

GURUPRASADB — Thu, 02 Jan 2020 08:11:09 GMT

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.

Error:

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

Data source for dtc_noWorkOrder
Data source for dtc_rejected

Discover Data Sources

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

M Query:

let
BaseUrl = "https://forms.logiforms.com/api/1.0/form/371533/data?",
InfoUrl = "https://forms.logiforms.com/api/1.0/form/371533/",
Token = "YXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
EntitiesPerPage = 100,

GetJson = (Url) =>
let Options = [Headers=[Authorization = "Basic " & Token ]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,

GetEntityCount = () =>
let Url = InfoUrl,
Json = GetJson(Url),
Count = Json[data][form][submissions]
in Count,

GetPage = (Index) =>
let Skip = "page=" & Text.From(Index),
Url = BaseUrl & Skip,
Json = GetJson(Url),
Value = Json[data][records]
in Value,

EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 1 .. PageCount },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),

#"Converted to Table" = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"datesubmitted", "Timecard_RecordID", "lfuuid", "User_Name", "User_RecordID", "Rejection_Reason", "User_Functional_Area", "RecordID", "Rejection_Code"}, {"datesubmitted", "Timecard_RecordID", "lfuuid", "User_Name", "User_RecordID", "Rejection_Reason", "User_Functional_Area", "RecordID", "Rejection_Code"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"datesubmitted", type datetime}, {"Timecard_RecordID", Int64.Type}, {"lfuuid", type text}, {"User_Name", type text}, {"User_RecordID", Int64.Type}, {"Rejection_Reason", type text}, {"Rejection_Code", type text}, {"RecordID", Int64.Type}, {"User_Functional_Area", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"datesubmitted", "lfuuid", "RecordID", "Timecard_RecordID", "User_Name", "User_RecordID", "User_Functional_Area", "Rejection_Reason", "Rejection_Code"}),
#"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Rejection_Description", each (
if [Rejection_Code] = "Z001" then
"Wrong Work Order"
else if [Rejection_Code] = "Z002" then
"Wrong Segment"
else if [Rejection_Code] = "Z003" then
"Time Not Approved"
else if [Rejection_Code] = "Z004" then
"Incorrect Day / Time"
else
""
))
in
#"Added Conditional Column"

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

Anonymous — Sat, 29 Feb 2020 05:57:06 GMT

I'm working a case with Microsoft about this very issue. The problem is certain calls can only be refreshed on the Power BI Service if the URL is hardcoded. Using an expression or variable, while allowed in Power BI Desktop while authoring, is rejected when you attempt to refresh or schedule the refresh.

More information is in my original reply addressing this issue:

https://community.powerbi.com/t5/Service/Scheduled-Refresh-Query-contains-unsupported-function-Odbc/m-p/953664/highlight/true#M88887

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

my_bi_quest — Tue, 21 Apr 2020 20:49:34 GMT

Hi fellas,

I fixed this situation while breaking up the URL into parts thanks to this post :

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

You basically need to hardcode the first part of your URL and continue with your variables for the rest of the implementation.

In my case it was a JIRA looping, hope the code below helps:

let
JQL="yourJQL",
EntitiesPerPage = 50,

EntityCount = List.Max({ EntitiesPerPage, GetEntityCount(JQL) }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPageValues(_, EntitiesPerPage, JQL)),
Entities = List.Union(Pages),
#"Converted to Table" = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key"}, {"Column1.key"})
in
#"Expanded {0}"

###################################

let
GetEntityCount = (JQL as text) =>
let
Json = GetJson(0, JQL),
Count = Json[total]
in Count
in GetEntityCount

####################################

let
GetJson = (StartAt as number, JQL as text) =>
let
RawData = Web.Contents("https://yourcompany.atlassian.net",
[
RelativePath="rest/api/3/search",
Query=[
jql=JQL,
startAt=Text.From(StartAt)
],
Headers=[Accept="application/json"]
]),
Json = Json.Document(RawData)
in Json
in GetJson

####################################

let
GetPageValues = (Index as number, EntitiesPerPage as number, JQL as text) =>
let
Json = GetJson((Index * EntitiesPerPage), JQL),
Value = Json[#"issues"]
in Value
in GetPageValues

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

Anonymous — Thu, 28 May 2020 14:24:54 GMT

@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!!! 😄

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

kleroy — Tue, 02 Jun 2020 01:36:23 GMT

@Anonymous Hoping someone can help! I have experienced same issues and was happy to see this post. Just altered my code to use a hardcoded first url and its still failing! My code is below, i am passing in a calcuated field called apiurl that is a concatenation of the remainder of the url and my variable.

Any help is appreciated! It refreshes in the desktop no problems but fails in the online service with error

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

Web.Contents(
"https://lhca.teamwork.com/tasks/",
[
RelativePath=[apiurl]
]
)

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

my_bi_quest — Tue, 02 Jun 2020 02:37:29 GMT

Hi @kleroy

You should remove the relative path "/tasks/" from your hardcoded URL. It should be https://lhca.teamwork.com instead.

Here an example for the Confluence API.

let
GetConfluContent = (id as number) =>
let
RawData = Web.Contents("https://yourcompanyname.atlassian.net",
[
RelativePath="wiki/rest/api/content/"&Text.From(id)&"?expand=children.attachment",
Headers=[Accept="application/json"]
]),
Json = Json.Document(RawData)
in Json
in GetConfluContent

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

osandrolucas — Thu, 04 Jun 2020 12:55:37 GMT

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?

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

Anonymous — Fri, 05 Jun 2020 08:24:46 GMT

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 🙂

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

osandrolucas — Mon, 08 Jun 2020 16:58:55 GMT

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

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

Anonymous — Tue, 09 Jun 2020 07:59:32 GMT

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

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

Anonymous — Tue, 07 Jul 2020 02:36:15 GMT

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

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

my_bi_quest — Tue, 07 Jul 2020 03:33:23 GMT

Hi @Anonymous.

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!

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

Anonymous — Fri, 10 Jul 2020 02:06:06 GMT

(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

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

MatiasVizzari — Tue, 21 Jul 2020 13:33:44 GMT

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"

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

Element115 — Sat, 10 Dec 2022 09:33:09 GMT

https://excelguru.ca/power-query-errors-please-rebuild-this-data-combination/