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

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

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!