Forum Discussion
Sometimes Power BI ignores data when refreshing inputs from Excel sheets that have formatted cells
I've noticed that sometimes, Power BI will skip rows of data when refreshing inputs that use long excel spreadsheets!
These are typically exported from an internal company data source and have simple, standard excel formatting on them (light blue tables).
At first I thought the data source itself was missing values, but checked first and the values were there. It's almost as is Power BI got bored after getting so far down the sheet, then gave up!
A temporary workaround has been to open each sheet manually, select all cells and then remove all formatting, but this is a pain in the arse, frankly speaking.
3 Replies
Hi Griff - This issue with Power BI skipping rows in large Excel files during data refreshes is fairly common, especially when dealing with files that have complex or inconsistent formatting, hidden rows/columns, or specific Excel features like merged cells
Try saving the Excel file in a different format, such as .xlsx (if it’s currently in .xls) or even as a CSV file. This can strip out any hidden formatting that might be causing issues and can often resolve data reading problems in Power BI.
Open the file in Excel and use Power Query (Get & Transform Data) to load the sheet. In Power Query, you can:
Remove unnecessary formatting, empty rows, or columns.
Standardize the data structure.
Save the cleaned data as a new worksheet or table within the same Excel file.
Then, in Power BI, connect to the newly transformed data instead of the original sheet.In Excel, ensure that your data is explicitly defined as a Table (using Insert > Table in Excel). This can sometimes make data import more reliable as Power BI recognizes the entire table range explicitly.
Avoid having multiple tables on the same sheet, as this can cause confusion with range detection.In Excel, go to each sheet, select all rows/columns, and Unhide any hidden rows/columns.
Delete any blank rows or columns at the end of the data range (especially if you notice blank rows being imported into Power BI).These are the steps we follow when we format, hope this helps.
That's all sound advice, but I'm using an app that exports Excel sheets which, although not complicated, have some formatting in them. There are also over a hundred of them, so as you can imagine it's tiresome stripping out the formatting manually. I did have something in place to take them out automatically, but since this was not 100% reliable it couldn't be trusted (& I need this to be 100% reliable)
Hi Griff -If your organization uses Microsoft 365, you could consider Power Automate to process these Excel files in the cloud. Power Automate has actions for reading and writing Excel data, which could reformat the file contents by moving data to a new Excel sheet without any formatting, although this is more complex to set up.
This PowerShell solution should provide the automation and reliability you need to avoid manual intervention while ensuring clean, formatting-free files for Power BI. Let me know if you need further customization with this approach!
Hope this information helps.
