Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Everyone,
I have been working on this for awhile, but I am just lacking a proper understanding of how Web.Contents is used between the Desktop and Service. Here is my delima:
I have Power M query to iterate page by page from an API (thank you @ImkeF for you wonderful assistance), which works just find from the Desktop.
let
WebAPI = "https://api.getdata.com/surveys/clientid/responses",
BearerToken= "Bearer XXXXXXXXXXXXXXXXX",
PageLimit = 20,
Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter = 0],
each (try List.IsEmpty([WebCall][client_data])=false otherwise false or [Counter]=0) and [Page]<=PageLimit,
each [ WebCall = Json.Document(Web.Contents(WebAPI&"?per_page=100&page="&Text.From([Page]),[Headers=[Authorization=BearerToken]])),
Page = [Page]+1,
Counter = [Counter]+1
]
))
in
Pagination
The troube begins when I publish to the Service and schedule the data for refresh: "Query contains unsupported function. Function name: Web.Contents". I have a loose understanding that the base URL isn't being recognized as a data source in the Service, but I do not know how to format the code in a way that trips up the Desktop to establish the base URL as a authenticated datasource that can be refreshed in the Service. What am I missing?
Thanks for the help in advance,
Woody
Solved! Go to Solution.
Hi @Anonymous ,
problem is the dynamic URL.
You have to use the relative path for it instead: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
based on this post
https://community.powerbi.com/t5/Power-Query/Exchange-rates-Data-not-refresh/m-p/909612
it seems that you need a gateway installed
@Stachu - Based on what the page you gave me, a gateway for web is "Required for .html, .xls, and Access Databases", but in my case, this is cloud based API, not something local. I have another Power M query which only requires one pass at the API and it can be scheduled in the Service without issue, so I am not sure it is the gateway is the answer.
Hi @Anonymous ,
problem is the dynamic URL.
You have to use the relative path for it instead: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF - I just had to take a fresh look, once we the Static Url was set, it worked like a charm. Here is the code snippet:
Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter = 0],
each (try List.IsEmpty([WebCall][active_models])=false otherwise false or [Counter]=0) and [Page]<=100,
each [ WebCall = Json.Document(Web.Contents("https://api.getdata.com",
[RelativePath="surveys/clientid/responses/?per_page=100&page=" & Text.From([Page]),
Headers=[Authorization="Bearer XXXXXXXXX"]])),
Page = [Page]+1,
Counter = [Counter]+1
Set the datasource "https://api.getdata.com" to Anonymous and then skip connection when it is the service.
@ImkeF - We started trying to give a static URL within the Web.Contents and add the Relative path, but we aren't getting an results. Here is what we changed:
let
BearerToken= "Bearer XXXXXXXXXXXXXXXXX",
PageLimit = 20,
Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter = 0],
each (try List.IsEmpty([WebCall][client_data])=false otherwise false or [Counter]=0) and [Page]<=PageLimit,
each [ WebCall = Json.Document(Web.Contents("https://api.getdata.com/surveys/clientid",
[RelativePath="/responses?page=" & Text.From([Page]),
Query=[Headers=[Authorization=BearerToken]]])),
Page = [Page]+1,
Counter = [Counter]+1
]
))
in
Pagination
Is there something wrong with syntax of the Relative Path parameter?
w.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.