Cannot load the file with xlsx into Fabric thru Gen2

[naoyixue1]

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!

[v-csrikanth]

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.

[v-csrikanth · Accepted solution]

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://yourten... 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://yourten... 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.

[v-csrikanth]

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!

Best Regards,
Cheri Srikanth 

[naoyixue1]

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

[miguel]

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

[v-csrikanth]

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

[v-csrikanth]

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

Thanks 
Cheri Srikanth

[naoyixue1]

@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?

[lbendlin]

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

[lbendlin]

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