Excel & Power Query data import error

[iosiflupis]

Hello all,

I have a problem working with Excel using Power Query to bring in 79 files with a total of 113MB. These files are .xlxs files averaging 1.4 MB. The files are located on a folder on my desktop. When I go through the power query process, I open PQ, then select the folder, Transform the files, then close and load. I have also tried to combine & transform the files. Once I press close & load the PQ window closes and then in about 30 seconds I get the following error:
Data could not be retrieved from the database.
Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again.
I have spoken with my IT personnel here and they have no idea what is going on. At first they thought that the folder might be on the drive for the airport and not on the local computer, but with it being on my desktop we had the same issue. I have also had this same issue with different folders and files. This has been going on for about a week or so.

Does anyone have ideas or solutions?

Thank you all!

 

[alansidman]

Have you considered that your copy of Excel may be corrupted and needs to be uninstalled and reinstalled?

 

[Kerryx]

Corrupt file or just dodgy quality data causing an error

 

[iosiflupis]

Corrupt file or just dodgy quality data causing an error

Kerryx,

This is happening with different data, and different sources (FAA and BTS). At first I thought it was bad data as some of the data was .xls. But I converted all data to .xlsx and am getting the same error. Also, when the data was native .xlsx I was getting the same error.

Right now I am open to any and all suggestions.

Funny thing is that I just ran a different excel + power query on completely different data that I had to convert from .xls to .xlsx. combined seven files and everything worked just fine.

 

[iosiflupis]

Update,

Kerryx, et. al. -- Seems that when the individual files are 15K+ rows, you cannot see the bottom. I had to open up the .xlsx file to see what was on the bottom. I tried to filter the first column as it had information in it, but would still crash at 1M+ rows loaded. Was still getting the Database error. Went to Power BI to try there. Would load about 1/2 the files and then give me a "Local "
error. still working on ways to clear out the bad data on the 'bottom' of each file. Unfortunately, I am having to open up each file to see what is on the bottom as when you click on the filter for a column it will only load 1K rows. 1/15th of what I have.

 

[iosiflupis]

Update #2

For anyone who comes across this problem in the future, here are some tidbits of knowledge.
1. Look at the raw data -- make sure that there isn't a single cell somewhere that has a hyperlink in it. For some reason the data that I was pulling from the FAA had a single cell among the 341,000+ cells.
2. Just because all of the previous files you downloaded from the same source were good, CHECK!!!
3. If you are having problems in excel+power query, switch to power BI=power query. PBI+pq will show you what files are working and where it crashed. This will allow you to take out the files that could be causing the issue. Makes it much easier to troubleshoot.
4. If your data has more than 1000 rows of data you will not be able to look at rows in the filter function in the power query editor, it has a max of 1000 rows to show. I made it easy by having a copy of the Excel spreadsheet open so that I could see what I was working with.
5. Expect to fight for this for a good period of the day. I have been working on it since 0730 today, and finally got it to go through the data completely at 1545.
6. Even when it works, there may be errors. I have almost 2M rows of data about airports nationwide. When I did a column chart the numbers I was getting didn't make sense. Back to the drawing board.

Thank you all for helping me along the way, and to KerryX. Now I know that when I get a database connection error, or an OLE DB or ODBC error it really means that the data is corrupt.