topic Re: Cannot load the file with xlsx into Fabric thru Gen2 in Dataflow

Cannot load the file with xlsx into Fabric thru Gen2

naoyixue1 — Thu, 17 Apr 2025 21:44:17 GMT

Hey all,

I tried to load the file from sharepoint to Fabric with Gen2 but it shows format error, but I really can open and see the file in sharepoint. (see below error message)

1: I tried to apply EXCEL.WOKBOOK to pull the content.

2: I tried to open the file directly but also failed

This is the source where I uploaded into sharepoint with 'xlsx' format and I can open and read this file.

Do you have any thoughts or idea to solve that fomat issue? Thanks!

Re: Cannot load the file with xlsx into Fabric thru Gen2

lbendlin — Thu, 17 Apr 2025 22:21:22 GMT

Power Query is case sensitive. Try Excel.Workbook or click on the link under [Content].

Re: Cannot load the file with xlsx into Fabric thru Gen2

naoyixue1 — Fri, 18 Apr 2025 16:19:49 GMT

lbendlin Thanks, but it still doesn't work. I think overall it's a format issue, since I directly click to explode the file, it still says format issue.
Hey all, any other thoughts?

Re: Cannot load the file with xlsx into Fabric thru Gen2

lbendlin — Fri, 18 Apr 2025 16:25:39 GMT

Maybe the file is not actually an xlsx, maybe it is a xls or csv file with the wrong extension.

Re: Cannot load the file with xlsx into Fabric thru Gen2

v-csrikanth — Mon, 21 Apr 2025 07:21:06 GMT

Hi naoyixue1
As suggested by lbendlin could you confirm the which extension are you using to pull the data.

Thanks
Cheri Srikanth

Re: Cannot load the file with xlsx into Fabric thru Gen2

v-csrikanth — Fri, 25 Apr 2025 07:47:27 GMT

Hi naoyixue1
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Thanks
Cheri Srikanth

Re: Cannot load the file with xlsx into Fabric thru Gen2

v-csrikanth — Thu, 01 May 2025 12:11:22 GMT

Re: Cannot load the file with xlsx into Fabric thru Gen2

naoyixue1 — Fri, 02 May 2025 16:13:05 GMT

v-csrikanth Hey Cheri, sorry i still got the same issue even following the suggested solution.

Re: Cannot load the file with xlsx into Fabric thru Gen2

miguel — Fri, 02 May 2025 19:24:25 GMT

If you try to open the file locally in Power Query for Excel or Power BI Desktop using the Excel connector, what error do you get?

Furthermore, if you use the excel connector in Dataflows and upload a copy of that same file, what error do you get when you connect to the file? Could you please share a screenshot?

Do try to save the file as a different format such as .xlsm and then back to xlsx as that'll try to fix the corrupted bits that a file may have as long as such process is done entirely through Excel

Re: Cannot load the file with xlsx into Fabric thru Gen2

v-csrikanth — Fri, 13 Jun 2025 11:54:31 GMT

Hi naoyixue1
Please do follow the steps that might resolve your issue:

The error occurs because Power Query is not receiving the file as a true binary Excel file, even though it ends with .xlxs.
Copying the SharePoint folder path or using a shared link doesn’t return the actual file stream but a webpage or metadata reference.
Go to the SharePoint library, click the three dots (...) next to the file, and select Copy link.
Modify the copied URL to use the download format:
https://yourtenant.sharepoint.com/sites/YourSite/_layouts/15/download.aspx?SourceUrl=https://yourtenant.sharepoint.com/sites/YourSite/Shared Documents/PacingTest.xlsx
In Power Query, use this adjusted URL with Web.Contents and pass it to Excel.Workbook, like this:
let
Source = Web.Contents("https://yourtenant.sharepoint.com/sites/YourSite/_layouts/15/download.aspx?SourceUrl=https://yourtenant.sharepoint.com/sites/YourSite/Shared Documents/PacingTest.xlsx"),
ExcelData = Excel.Workbook(Source, null, true)
in
ExcelData

This will make sure Power Query receives the file as a valid .xlxs package and avoids the format or missing main part errors.
Make sure the file isn’t checked out or locked, and that you have permission to access it using the same credentials in Power BI/Fabric.
This method is confirmed and commonly used when SharePoint files show as accessible in the browser but fail in Power Query.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

Re: Cannot load the file with xlsx into Fabric thru Gen2

v-csrikanth — Tue, 17 Jun 2025 14:03:47 GMT

Hi naoyixue1
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.