PowerBI error when importing large excel worksheet

Question

I'm trying to load an excel worksheet into PowerBI Desktop and when it reaches around 50 MB, it fails with the error "Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataFormat.Error] The input couldn't be recognized as a valid Excel document.. '."

If I reduce the size of that worksheet to about half, it loads fine.

The other worksheets in that doc (all are much smaller) load perfectly. I tried loading the failing worksheet first and get the same error. If I load the other worksheets first, they load fine but when I add the failing worksheet, I get the same error for all the worksheets.

Can someone suggest anything that I could try to resolve this?

Thanks in advance V

Answer 1

As the error message indicates, the problem may be caused by a formatting error.Get a list of files found in a OneDrive folder, you can try again by following these steps:

1. Get a list of files (URLs) via Source=SharePoint.Tables("https://your.sharepoint.com/personal/john_doe_acme_com/",[ApiVersion=15])

2. Expand Documents "Table"

3. Expand "File.LinkingUrl" and split by "?" to get a clean URL

4. Do filter the desired folder (via URL or Folder.Name or other attribution)

5. Add a Custom Column = Excel.Workbook(Web.Contents([File.LinkingUrl))

Click this link for detailed information: https://community.powerbi.com/t5/Desktop/Error-when-connecting-folders-containing-excel-files/m-p/2343510#M845219