Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GURUPRASADB
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"

14 REPLIES 14
my_bi_quest
Frequent Visitor

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!

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"
my_bi_quest
Frequent Visitor

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

Anonymous
Not applicable

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

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

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 🙂

Anonymous
Not applicable

(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

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

Anonymous
Not applicable

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

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.

 

 

 

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

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

T2
Helper II
Helper II

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.