Memory error when refreshing certain data from Access database

[Migui]

Hello,

I have a PowerBI file that uses an Access database as a data source for its queries. There is a 'master table' which contains all of the records, but I query that into a smaller table, which is the table that PowerBI connects to. I've used this setup for about a year now and hadn't encountered any problems until now, even with 1 million+ rows in the table.

Recently I've merged the Access database with another database that contains legacy data. This legacy data has the same format as the new data; I just imported more rows into the same table (I do this on a monthly basis to add new data to the database).

My problem is that if I try to refresh the data source in PowerBI, when there is legacy data in the source table, then the memory and disk usage of my computer are maxed out. But when I try without the legacy data, the refresh works just fine. (even if it is 1,000 rows of legacy data vs. 200,000 rows of new data, the legacy data will freeze while the new data will load perfectly)

I've tried changing the Auto Date/Time, Parallel Loading and Data Preview settings in the Options -> Data Load window, but those did not help.
I've also tried loading this data into a new PowerBI file and it loads just fine. So it is a problem with the PowerBI file + the legacy data.

I'm not sure why this specific data does not work, and any help would be appreciated!

Thank you! 🙂

[edhans]

Are you running the 32bit version of Power BI Desktop or the 64bit version? I saw all sorts of memory issues with the 32bit version when I first started using PBI last year when I was pulling from tables with millions of records. Switched to Office 64 bit and PBI 64 bit and haven't seen them since.

[Migui]

I'm currently using the 64-bit version of PowerBI.

It's just these rows that are causing the problem, unfortunately 😞 even if I only import 1,000 rows, PowerBI still freezes on me.

[edhans · Accepted solution]

Try a transformation on the data to CLEAN it. It may be that there are unprintable characters in there that are causing issues. I'm just speculating here as I don't have the actual data to play with. I'd also take some of that data and put it in Excel and use CODE() on it to see if there were any unusual chars causing issues that visually are difficult to see. 

[Migui]

That worked, thank you very much!