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.
