Best practice to load data from ElasticSearch to PowerBI

[NielsT]

I'm trying to get data out of ElasticSearch and into PowerBI. What is the best practice to extract data?

I have already tried the script below but this gives a time out error. I think the table is loaded first, before the filter is applied (Number of rows). It's a table with a lot of rows.

let
Source = Json.Document(Web.Contents("https://dashboard.xxxxx.com/api/search/lwqanda")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"QandAID", "ActionID", "QuestionID", "AnswerID", "Description", "Hyperlink"}, {"QandAID", "ActionID", "QuestionID", "AnswerID", "Description", "Hyperlink"}),
#"Kept Last Rows" = Table.LastN(#"Expanded Column1", NumberOfRows)
in
#"Kept Last Rows"

Table

* There is no date/time field in the table.

[Anonymous · Accepted solution]

Hi @NielsT ,

1. You could try to firstly keep the Top100 rows before covert to table or expand.

2. Please specify timeout interval in Advanced Editor manually, for example

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.