Error when connecting folders containing excel files

[Absalon29]

I'm trying to get data from folders in my OneDrive for Business folder. Every folder I try and connect to throws the same 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..

Any ideas re what I'm doing wrong?

[v-henryk-mstf · Accepted solution]

Hi @Absalon29 ,

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))

For more details, you can read related blog as below:

Combining Excel Files hosted on a SharePoint folder with Power BI | Microsoft Power BI Blog | Micros...

Power BI > Get Data from OneDrive for Business (Folder as a source) – Inkeysolutions

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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