Forum Discussion
Cannot load the file with xlsx into Fabric thru Gen2
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!
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.-
10 Replies
Power Query is case sensitive. Try Excel.Workbook or click on the link under [Content].
Maybe the file is not actually an xlsx, maybe it is a xls or csv file with the wrong extension.
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 SrikanthHi 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!
Best Regards,
Cheri Srikanthv-csrikanth Hey Cheri, sorry i still got the same issue even following the suggested solution.
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
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.-
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.
