How to get all data from elasticsearch to Power bi

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

From Elastic Search to Elasticsearch

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.