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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
fmooura
Regular Visitor

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors