Grabbing data from Excel File on Sharepoint results in stack overflow

[ataladayFBG]

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 add column to say it is an excel workbook, but when I expand that table to see the items, kind, etc.  I get the stack overflow error.  I've tried to filter to only sheet's to lessen the data, but still get the error.

section Section1;

shared Query = let

  Source = SharePoint.Files("https://REDACTED/sites/CashLog/", [ApiVersion = 15]),

  #"Filtered rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),

  #"Filtered rows 4" = Table.SelectRows(#"Filtered rows", each Text.Contains([Folder Path], "CashLog/Cash Log")),

  #"Filtered rows 1" = Table.SelectRows(#"Filtered rows 4", each Text.Contains([Name], "Cash Log")),

  #"Filtered rows 2" = Table.SelectRows(#"Filtered rows 1", let latest = List.Max(#"Filtered rows 1"[Date modified]) in each [Date modified] = latest),

  #"Added custom" = Table.AddColumn(#"Filtered rows 2", "isExcel", each Excel.Workbook([Content])),

  #"Expanded isExcel" = Table.ExpandTableColumn(#"Added custom", "isExcel", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"})

  in

   #"Expanded isExcel";

[Anonymous]

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]

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.