.xlsx file not reading correctly until resaved

[Anonymous]

Hello, I am having an issue with Power BI reading data from a .xlsx file downloaded from the Internet. The web application that generates this file is likely somewhat dated, but the file exports just fine as a .xlsx. The most obvious issue that the first several rows of the Excel file contain merged cells, formatting etc, but I don't think that this is causing the problem.

What I am trying to do is merge around two dozen of these files, each using the exact same formatting but each containing multiple lines of desired data. When I download these files, I place them in one folder and use the Get Data 'Folder' option to load all the files at once (these file names vary from month to month, so it wouldn't work to open them individually by name. I encounter this same issue when opening these files individually). When Power BI loads them, it reads the first few rows (containing the merged cells/formatting, which I remove later), and only one row of my data.

My original dataThe raw data, right after it's pulled into PBI

The only workaround I have found for this is to mass-open all of my files, CTRL+S (save) them, and exit. Immediately after doing this and refreshing, PBI reads the full set of data.

After I re-save all of the files and refresh PBI, all of my data appears

Has anyone encountered this issue before, and is there a way to fix it without the resaving-files workaround? Is it an issue with my files, or with PBI?

[Anonymous]

I am having the same issue!  We've identified that it is being caused by the Excel file first opening in read-only.  Once it has been opened and then saved, the read-only is removed. My issue is that we have the file replaced each day, so continue with this issue. We are using PowerAutomate to open and save the file.

[SteveDorr]

Hi

It is 2022 and I am having this problem as well with Excel and Power Bi only reading 1 line of data.  If I resave the Excel file it works great.  This is the first time I have run into this issue.  The Excel file is being generated by software called "Orchestrator" which is an automation tool.  It produces a log of activities I want to be able to build a PBI dashboard.  But when I read this file, I only get one line unless I resave it. 

It is easily reproducable, and I am happy to send Microsoft a copy of the Excel file for them to look at, to determine what the issue is.  I'm certain that Orchestrator is not producing a "pure" Excel file, but it looks fine inside Excel when I bring it up.

[Ashish_Mathur]

Hi,

Follow the steps outlined here.

Hope this helps.

[v-lili6-msft]

hi, @Anonymous 

I have test on my side, but not reproduce the issue.

Could you try to update to the latest version: (2.70.5494.761) and try it again?

https://www.microsoft.com/en-us/download/details.aspx?id=45331

And It's a good practice to format range to table in excel file. A table has clear border of rows and columns.

Best regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

I checked my version of PBI and I am up to date with the version you pointed out.

Power BI version 2.70.5494.761 64-bit

Is it possible to make Power BI read the sheet correctly without modifying the Excel sheet (to turn it into a proper table)? I suspect you will be unable to duplicate the issue; I only experience the issue with these specific downloaded files, and it seems like it's probably something weird going on with the database report generator. Have you ever experienced a similar issue in another setting, or are there additional tweaks I can make to the PBI Excel-loading sequence? I'm trying to avoid making a mass-edit to the Excel files if possible.

Thank you

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

1. It's better modifying the Excel sheet before import data from excel file, that will be a great help for you transform data in edit queries.

https://radacad.com/power-bi-get-data-from-excel-everything-you-need-to-know

2. I couldn't duplicate the issue and I haven't had this kind of issue for now.

I think there should be something wrong in these excel file, it's better check the excel files.

Best Regards,

Lin

[florent-3628]

Hello,

i've a similar issue ; when powerbi import the file , it did read the columns but nothing else... 

There's two problem:

 1= Powerbi see the file empty

 2= Powerbi act like anything is normal without any errors (so the reports linked to that file became empty too without any alert...)

[Anonymous]

@v-lili6-msft 

I am pretty sure that the issue must lie solely with the Excel sheet; I am still trying to find ways to make Power BI read the files correctly but for now I think the answer is that the files are corrupted/broken.

Thanks!