Forum Discussion
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 :
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
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!!! 😄
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
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?
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 PageResultWhen I will use "https://xxxx.xxxx.net/Test/v3" as a first parameter in Web.Contents it won't work 🙂
