how to create a query that paginates?

Hi All,

I just wanted to share some code I wrote to get data out of elastic search using its scan and scroll pagination.

So if you have lots of data in elastic search, you must grab it in batches, and this code does exactly that, perhaps it will be helpful to someone:

let
FnGetElasticData = (url as text) as list =>
let
	// init params
	size=10000, // the size of the "chunks" of data that will be returned on each batch
	scroll = "1m", // the time in minutes elastic search should keep the "context" alive
	// build url for first fetch
	operator = if Text.Contains(url, "?") then "&" else "?",
	initUrl = url & operator & "scroll=" & scroll & "&size=" & Text.From(size),
	
	// get first batch of results
	initSource = Json.Document(Web.Contents(initUrl, [IsRetry=true])),
	totalResults = initSource[hits][total], // the total number of results to return
	iterations = Number.IntegerDivide(totalResults, size), // the total number of iterations we need to do
	
	// build url for scroll
	scrollId = initSource[_scroll_id], // the scroll id with wich we will fetch the rest of the results
	uriParts = Uri.Parts(url), // for getting the host and scheme of the url
	scrollUrl = uriParts[Scheme] & "://" & uriParts[Host] & "/_search/scroll?scroll=" & scroll & "&scroll_id=" & scrollId,
	
	// this will return a scrolled result from elastic search
	FnGetScrolledPage = (url as text) as list =>
		let
			response = Json.Document(Web.Contents(url, [IsRetry=true])),
			data = response[hits][hits]
		in
			data,
	
	// now loop through all of the iterations and return the data
	resultsList = List.Generate(()=>[i=0, res=initSource[hits][hits]], // Set inital data
		each [i] <= iterations, // Keep going until all of the iterations have been done or there is no more data
		each [i=[i] + 1, res = FnGetScrolledPage(scrollUrl)], // Get next batch of results		
		each [res])
in
    resultsList
in
FnGetElasticData

Basically, it will create a function that you can call with the URL for your elasticsearch query and it will return all of the results using paging.

Please note that this code will not work in automatic refreshes using the Power BI Gateway because that expects static URLs, you can get over this pretty easily by changing the code above to include the hard-coded URLs and then scheduled refresh will work.