Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Absalon29
Helper III
Helper III

Error when connecting folders containing excel files

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?

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

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

vhenrykmstf_2-1645064312736.png

vhenrykmstf_3-1645064349526.png

 

 

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.

View solution in original post

1 REPLY 1
v-henryk-mstf
Community Support
Community Support

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

vhenrykmstf_2-1645064312736.png

vhenrykmstf_3-1645064349526.png

 

 

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.