Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power BI not Importing All Records

Hi All, 

 

I've got a very simple data table (although very large) from Access that I've tried to import into Power BI, however not all of the records are coming over. I don't have any filters on the data.

 

Out of the 1.65Mil records that I'm trying to import only 1.05Mil records are being imported. 

 

I know this is very vague, but I don't think there's many "moving parts" to my table, it's just big. Has anyone experienced this? 

 

Thanks in advance!

15 REPLIES 15
Anonymous
Not applicable

Hi @Anonymous ,

 

I am also experincing the same issue, was your issue resolved? If so, please let me know how.

MissyShealy
Frequent Visitor

Hello. I am experiencing the same problem.

 

I am new to Power BI Pro Desktop.

 

I imported an Excel file with ~ 212,000 records. Only 210,594 imported in along with a pivot table. I tried a larger file earlier and had the same experience. I thought my smaller file (212,000 records) would import in fine.

 

I have verified that I have the Pro version and currently have 0 MB of 10 GB used. 

 

What could I be doing wrong?

Anonymous
Not applicable

You know what helped me is to make sure the data that you're importing is in a table on your Excel sheet. 

 

That might help. 

Verified. The data is a table within Excel.

Anonymous
Not applicable

Is this data that you can share? If so, I can take a look at it to see if I can get the same issue.

pqian
Employee
Employee

I haven't heard anything like this. Definitely a serious issue if this is a bug. Can you use "Send a frown" button from the workbook that's missing data, and report the issue that way?

 

Another thing to try: PowerQuery for Excel, see if the issue repros there.

Anonymous
Not applicable

I'll try Power Query.

 

Just an update - I reuploaded my data table and it stopped at the exact same place. I haven't done a lot of testing, but it looks like PowerBI can only import 1,048,501 records in one table. 

@Anonymous it can definitely handle more than 1,048,501 records so don't discount it yet! I have tables with 5 mm + records I am working with.

Anonymous
Not applicable

@ryans Well that's good news and bad news then. I'm happy to hear that PowerBI should be able to handle the size, but now I'm left with my original problem. Cat Sad

 

I hope it's what @pqian said and it should be fixed soon.

@Anonymous Give Table.Buffer a try, if it is the issue I mentioned, then it means at 1mil rows the server returned an error, you still need to go chase that down.

 

Actually you can use any function that breaks folding to the DB and require buffering rows locally. A simple split column should do it too.

Anonymous
Not applicable

@pqian I tried giving Power Query a try and got the following message:

 

The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result.

 

 

That exception is coming from the Access DB...Can you take a screenshot of the error? Maybe your access file is located on a disk that's out of space?

Anonymous
Not applicable

@pqian I just ran the opened the database in Access and it didn't throw an error. I've also not said this because it's the Power BI community but my company also has Tableau and when I connect the table in Tableau I don't get an error either.

 

Sorry, I'm new to the "Power" Apps (PowerQuery, PowerPivot, PowerBI) and I'm sure I'm not being as helpful as someone who's a pro at them.

Anonymous
Not applicable

@pqian Scratch that. It loaded the table fine but when I tried to go to the last record it threw the error leting me know that either the size of the table is larger than 2GB or I don't have enough space on my disk.

@Anonymous This maybe related to an issue we uncovered recently around error handling during importing. I suspect that around 1 million rows there is an error and we aren't handling it correctly. This issue should be fixed soon.

 

Meanwhile, here are somethings you can try:

1. Split into multiple tables with Skip+Top, see if you can get to all the data that way

2. Add a Table.Buffer() to your query, that may have better error handling but will require large amount of memory.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.