I am using this query but it returns 10000
let
// Define Elasticsearch URL and Credentials
baseUrl = "", // Include username and password in the URL
indexName = "cube",
searchEndpoint = "/cube/_search",
scrollEndpoint = "/cube/_search/scroll",
// Initial API Call
initialUrl = baseUrl & searchEndpoint & "?scroll=1m&size=1000000", // Adjust size as needed
InitialResponse = Json.Document(Web.Contents(initialUrl)),
InitialHits = InitialResponse[hits][hits],
ScrollID = InitialResponse[_scroll_id],
// Function to get the next batch of results
GetNextScroll = (scroll_id as text) =>
let
url = baseUrl & scrollEndpoint,
body = "{ ""scroll"" : ""1m"", ""scroll_id"" : """ & scroll_id & """ }",
options = [
Headers = [
#"Content-Type" = "application/json"
],
Content = Text.ToBinary(body)
],
Source = try Json.Document(Web.Contents(url, options)) otherwise null,
Hits = if Source <> null then Source[hits][hits] else null,
NewScrollID = if Source <> null then Source[_scroll_id] else null,
Result = [Hits = Hits, ScrollID = NewScrollID]
in
Result,
// Loop to fetch all data
FetchData = List.Generate(
() => [Data = InitialHits, ScrollID = ScrollID, MoreData = true],
each [MoreData] and [ScrollID] <> null,
each
let
NextBatch = GetNextScroll([ScrollID]),
NewData = if NextBatch[Hits] <> null then NextBatch[Hits] else {},
NewScrollID = if NextBatch[ScrollID] <> null then NextBatch[ScrollID] else null
in
[Data = List.Combine({[Data], NewData}), ScrollID = NewScrollID, MoreData = List.Count(NewData) > 0],
each [Data]
),
// Loop to fetch all data
FetchData1 = FetchData{0},
expandedDocs = Table.ExpandRecordColumn(
Table.FromList(FetchData1, Splitter.SplitByNothing()),
"Column1",
{"_source"},
{"_source"}
)
in
FetchData1