Forum Discussion
Expression.Error: Evaluation resulted in a stack overflow and cannot continue.
Hi all,
Below is the update for this issue. (IcM: 219724212, 224968762)
This is caused by a common Mashup Engine bug which is currently under investigation. There is no ETA currently. Below are two workarounds to it:
- Workaround #1:
Call Table.AddIndexColumn on each source query before using Table.Combine. Or add a new step to each query using "Index Column" button under Add Column tab before combining tables.
- Workaound #2:
Call Table.Buffer on each of the tables being combined before calling Table.Combine.
Community Support Team _ Jing
- HectorSC4 years agoMicrosoft Employee
This totally worked for me for SP Lists I addedd Index from 0, thanks a lot
- MAAbdullah_474 years agoHelper V
Hi v-jingzhang could you re-explain the work around again , I didn't understand it ?
- v-jingzhang4 years agoCommunity Support
MAAbdullah_47 You can add a new step to each query using "Index Column" button under Add Column tab before combining tables.
- TrevorC4 years agoAdvocate I
It was the index for me.
Thank you
- nicpenning4 years agoHelper I
Hello! I am using a recursive function to append 10K results at a time. Do you think the following is affected by this bug?
Function:let RecursiveElasticFetch = (relativepa, relativepa2, batchSize, urlBody, scrollBody, scrollID, counter, scrollEND, apiKey) => let Results = if (scrollID = null) then //Initial Query Json.Document(Web.Contents("https://es:9200", [RelativePath=relativepa, Headers=[#"Authorization"=Text.Combine({"ApiKey ",apiKey,""}), #"Content-Type"="application/json"], Content = Text.ToBinary(urlBody)])) else //All other queries execute this to gather results from the scroll api even if the scroll id changes. Json.Document(Web.Contents("https://es:9200", [RelativePath=relativepa2, Headers=[#"Authorization"=Text.Combine({"ApiKey ",apiKey,""}), #"Content-Type"="application/json", MyHeader=Text.From(counter)], Content = Text.ToBinary(scrollBody&scrollID&scrollEND)])), //If this is the first time the function runs, the counter should be null so this will dynamically calcuate how many times this function needs to run. counter = if (counter = null) then //Dynamically get the counter - Note: You can uncomment the next line for testing and then comment out the Number.RoundUp //7 Number.RoundUp(Results[hits][total][value]/Number.FromText(batchSize)) else counter, //Store the hits from the ElasticSearch query into ParsedResults and if results already exist, append more results to generate the full table of events. ParsedResults = Table.FromList(Results[hits][hits], Splitter.SplitByNothing(), null, null, ExtraValues.Error), Return = if (counter > 0) then ParsedResults & RecursiveElasticFetch(relativepa, relativepa2, batchSize, urlBody, scrollBody, Results[_scroll_id], counter-1, scrollEND, apiKey) else ParsedResults in Return in RecursiveElasticFetchIf so, how would one apply a work around?
Here is a snippet of the query starting where the function above is called:Source = RecursiveElasticFetch(relativepa, relativepa2, batchSize, urlBody, scrollBody, scrollID, counter, scrollEND, apiKey), #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "es"}}), #"Expanded es" = Table.ExpandRecordColumn(#"Renamed Columns", "es", {"_index", "_source"}, {"_index", "_source"}), #"Expanded _source" = Table.ExpandRecordColumn(#"Expanded es", "_source", {"_label", "_createdAt", "data", "dataType", "tlp", "sighted", "_createdBy", "message", "ioc", "caseId", "relatedId", "tags"}, {"ObjectType", "DateCreated", "data", "dataType", "tlp", "sighted", "CreatedBy", "message", "ioc", "caseId", "relatedId", "tags"}), #"Expanded tags" = Table.ExpandListColumn(#"Expanded _source", "tags"), #"Duplicated Column" = Table.DuplicateColumn(#"Expanded tags", "DateCreated", "DateCreated - Copy"), #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"DateCreated - Copy", "TimeCreated"}}), #"Parsed Time" = Table.TransformColumns(#"Renamed Columns1",{{"TimeCreated", each Time.From(DateTimeZone.From(_)), type time}}), #"Parsed Date" = Table.TransformColumns(#"Parsed Time",{{"DateCreated", each Date.From(DateTimeZone.From(_)), type date}}) in #"Parsed Date"- Anonymous3 years agoNot applicable
Hi nicpenning , we're using the same Elasticsearch function and are experiencing the same issue with a index of about 1.5GB. Did you find any solutions for the stack overflow issue calling ES from PowerBI? Thanks
- nicpenning3 years agoHelper I
So I don't believe I could get the work around to work. I am running the October 2022 version of PBI and it will eventually refresh, but it takes a very long time. Are you using my function above?
The index we query is over 1 millions rows - The index is about 3.8 GB, but totals 7.6 GB with 1 primary and 1 replica. Currently timing the refresh, stay tuned.