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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Scheduled refresh not enabled - Power BI Service - Web Contents

Hi everyone, I'm trying to resolve a scheduled update issue on Power Bi Service.

I'm extracting data via WebContents with a recursive query, and in Power Bi Desktop it works perfectly.
The tutorial I used to pull the data is this:

https://community.powerbi.com/t5/Power-Query/Getting-Data-from-Elastic-Stack-with-REST-with-Scroll/t...

When I put it in the Power BI service, it says that it has a dynamic data source, and therefore it cannot update. I searched several articles around here, but I didn't find any that looked like my case. I wanted help to fix my code, can you help me?

My query is:

let
baseUrl = "http://xxxxx",
url = Text.Combine(baseUrl,"/orders/_search"),
scrollURL = Text.Combine(baseUrl,"/_search/scroll"),
relativepa = "/orders/_search",
relativepa2 = "/_search/scroll",
batchSize = "10000",
urlBody = Text.Combine({"{""size"":",batchSize,",""query"": { ""range"": { ""created_at"": { ""gte"": ""2022-01-01"" } } }, ""_source"": [""id"", ""created_at"", ""new_statusTranslate"", ""total"", ""pharmacy_id"", ""pharmacy_name"", ""payment_method"", ""client_id"", ""user_id"", ""payment.provider"", ""state_name"", ""antifraud.sessionId"", ""isMultipleCheckout"", ""WithCoupon"", ""cancellation_reason_translate""] }"}),
scrollBody = "{""scroll"":""1m"",""scroll_id"":""",
scrollID = null,
counter = null,
scrollEND = """}",
Source = RecursiveElasticFetch(baseUrl, relativepa, relativepa2, batchSize, urlBody, scrollBody, scrollID, counter, scrollEND)
in
Source



And the function is this:

let
RecursiveElasticFetch = (baseUrl, relativepa, relativepa2, batchSize, urlBody, scrollBody, scrollID, counter, scrollEND) =>

let
Results = if (scrollID = null) then
//Initial Query
Json.Document(Web.Contents(baseUrl, [RelativePath="/orders/_search", Query=[scroll="1m"], Headers=[#"Authorization"="Basic xxxxxx", #"Content-Type"="application/json"], Content = Text.ToBinary(urlBody)]))
else
//All other queries execute this to gather results from the scroll api even if the scroll id changes.
Json.Document(Web.Contents(baseUrl, [RelativePath="/_search/scroll", Headers=[#"Authorization"="Basic xxxxx", #"Content-Type"="application/json", MyHeader=Text.From(counter)], Content = Text.ToBinary(scrollBody&scrollID&scrollEND)])),

//If this is the first time the function runs, the counter should be null so this will dynamically calcuate how many times this function needs to run.
counter = if (counter = null) then
//Dynamically get the counter - Note: You can uncomment the next line for testing and then comment out the Number.RoundUp
//7
Number.RoundUp(Results[hits][total][value]/Number.FromText(batchSize))
else
counter,

//Store the hits from the ElasticSearch query into ParsedResults and if results already exist, append more results to generate the full table of events.
ParsedResults = Table.FromList(Results[hits][hits], Splitter.SplitByNothing(), null, null, ExtraValues.Error),

Return = if (counter > 0) then
ParsedResults & RecursiveElasticFetch(baseUrl, relativepa, relativepa2, batchSize, urlBody, scrollBody, Results[_scroll_id], counter-1, scrollEND)
else
ParsedResults
in
Return
in
RecursiveElasticFetch


I've seen it in some posts about the relativepath, but even changing it seems that there is something that the power bi service is understanding that it is dynamic, but I have little knowledge.

0 REPLIES 0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Solution Authors
Top Kudoed Authors