Forum Discussion
Data Not Populating in Table View
I am trying to insert a spreadsheet into PowerBI, but the data is not populating in Table View. The headers are present, but no data populates below each field. When I go to insert the data into PowerBI, the data is visible in the Navigation Panel but disappears once everything is uploaded. I have about four other tabs/spreadsheets from the same Excel file uploading and populating with no problems. Once the loading process is completed for all the spreadsheets, no error/issue message is displayed and the dialogue box indicates that everything was successfully uploaded. I have deleted and reinserted the spreadsheet into the model several times and have restarted the application but I have had no luck resolving the issue. Looking for some insight on how to fix this.
6 Replies
- HotChilli
Community Champion
It won't be affected by restarting the app - when Power Query loads the data it follows a 'recipe' to select the appropriate data from the source (Excel in this case) so you need to focus your investigation on the Power Query part of this.
Hit the Transform Data button instead of loading the data and give us some screenshots or something else to help you.
- HotChilli
It looks like it has loaded but the data has ordered to show predominantly null values. Are there more rows with data? Try filtering from one of the column headers to see if all the values are shown. Try re-ordering by one of the columns which you know has data (a possible primary key).
Also, at the bottom of Data View it will tell you how many rows have loaded (See if that matches what you are expecting)
Hi there, I'm a little embarrassed to say this, but I had a similar problem... It seems that when someone formats a lot of empty rows in Excel, Power BI may treat those rows as valid records with null values and display them first. That's why it can look like there's no data. It also explains why applying a filter seems to fix the issue. 😅
I spent an hour figuring this out. In the end, I simply filtered out null values from the ID column, which should always contain a value, and the problem disappeared!
I need a beer now. 🍺
RossEdwardsRioResolver I
if you are curious why. Excel stores in its files what the "last row" and "last column" of the used space is. So if someone formats an entire column to the end of the sheet, the last row can be considered some really large number. A similar thing can happen with columns, you'll notice it if you have extra columns suddenly appear in Power BI that say something like "Column7".
Fixing the excel sheet is usually the best outcome, you just highlight the offending rows/columns and mark "delete rows/columns".
If you can't fix the source, you are correct that you just need to filter blank rows, or use a "remove other columns"
- uzuntasgokberk
Super User
Check the row count at the bottom of Table View. If it shows rows but the grid looks blank, open the filter on a column that every valid row must have, such as an ID, and uncheck (Blank). If your records appear, Power BI loaded the data along with blank worksheet rows.
For a lasting fix, turn the actual data range into a named Excel Table, then point the existing Power Query to that table instead of the entire sheet. If you can’t change the workbook, filter out nulls in that required column in Power Query and select Close & Apply.
If Table View shows zero rows after you clear its filters, open Transform Data and click through Applied Steps from Navigation onward. Check the first step where the rows disappear.
