# How to get all data from elasticsearch to Power bi
**URL:** https://discuss.elastic.co/t/how-to-get-all-data-from-elasticsearch-to-power-bi/362471
**Category:** Elasticsearch
**Created:** [July 3, 2024, 1:43pm UTC](https://discuss.elastic.co/t/how-to-get-all-data-from-elasticsearch-to-power-bi/362471 "2024-07-03T13:43:00Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@Ali\_Salim](https://discuss.elastic.co/u/Ali_Salim)
#### Post date: [July 3, 2024, 1:43pm UTC](https://discuss.elastic.co/t/how-to-get-all-data-from-elasticsearch-to-power-bi/362471/1 "2024-07-03T13:43:00Z")
I am using this query but it returns 10000
```auto
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
```
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [July 3, 2024, 2:41pm UTC](https://discuss.elastic.co/t/how-to-get-all-data-from-elasticsearch-to-power-bi/362471/2 "2024-07-03T14:41:48Z")
From #Elastic Search to #Elasticsearch
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 31, 2024, 2:42pm UTC](https://discuss.elastic.co/t/how-to-get-all-data-from-elasticsearch-to-power-bi/362471/3 "2024-07-31T14:42:24Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.