Forum Discussion

ataladayFBG's avatar
ataladayFBG
Frequent Visitor
2 years ago

Grabbing data from Excel File on Sharepoint results in stack overflow

Help!   I've had this query for awhile, but now it is giving me an error.   I point to my sharepoint site.  Filter to the folder path and the file name. Filter to Latest on date modified.  Then a...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi ataladayFBG ,

Based on your description and the provided M code, it appears that you are trying to access an Excel file from SharePoint, then filter and expand the contents. The stack overflow error you are experiencing is likely due to the complexity of the data and the operations being performed, which can exceed the memory limits of Power Query.

Please try:
1. Break down the query into smaller parts and load them separately to see if a particular step is causing the issue.
2. Simplify your query to see if the error persists. Here's an example of how you might simplify it:

 

let
  Source = SharePoint.Files("https://REDACTED/sites/CashLog/", [ApiVersion = 15]),
  FilteredRows = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
  LatestFile = Table.SelectRows(FilteredRows, each [Date modified] = List.Max(FilteredRows[Date modified])),
  GetContent = Table.SelectColumns(LatestFile, {"Content"}),
  GetWorkbook = Table.TransformColumns(GetContent, {"Content", each Excel.Workbook(_)})
in
  GetWorkbook

 

If the simplified query works, gradually add more steps back in to identify which step is causing the stack overflow error.
3. Try to use 'Table.Buffer' to buffer your data into memory, which can sometimes help avoid stack overflow errors.
Table.Buffer - PowerQuery M | Microsoft Learn

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

  • ataladayFBG's avatar
    ataladayFBG
    Frequent Visitor
    2 years ago

    Thank you for your help. 

     

    I tried your steps, but when I expand the Content to show item, kind, etc. It gives the same error.  I added the Table.Buffer right before that step.  I'm guessing there is something corrupted in the file.  I have similar steps on files that are larger.  And I try and filter/narrow the results before expanding so the portion it is expanding is smaller and I'm still getting it.

     

    I will be testing the step

    Table.TransformColumns(GetContent, {"Content", each Excel.Workbook(_)})

    versus my go to Table.AddColumn(#"Filtered rows 2", "isExcel", each Excel.Workbook([Content])).  I'm always happy to use cleaner code that yields the same end results.