cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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.

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"

13 REPLIES 13
Highlighted
Advocate I
Advocate I

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

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

Highlighted
Frequent Visitor

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

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

 

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

Frequent Visitor

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

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

Highlighted
New Member

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

@ptrk   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]
]
)

Highlighted
Frequent Visitor

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

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

Highlighted
Frequent Visitor

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

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?

Highlighted
Frequent Visitor

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

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 🙂

Highlighted
Frequent Visitor

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

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:

Screenshot_2.jpg

 

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

Highlighted
Frequent Visitor

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

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors