Speed Up Excel File Load

[Anonymous]

Power BI has a big issue when trying to load Excel files as a data source. The files could be very small (only a couple of megabytes), but if you do the wrong combination of steps (such as an append), the load is extremely slow. Even a straight load is very slow, for the size of the file.

I suggest that Power BI should load the Excel files once into memory, in Power BI's native table format (or whatever in memory), and then refer to that for all future steps.

Currently it will re-read the same file(s) from disk, many, many times - depending on the steps performed. I'm unsure why this overhead is needed.

If I was to perform the equivilant load and data maniputation using Python Pandas, it would load the excel files once, perform all the data manipulation, and be done in a fraction of the time.

Microsoft really need to pick up the game on this. I have no idea why they treat an excel file like a database. I lose hours of time whenever I deal with excel files, because of this. The worst is when you have a folder of excel files. 

[v-yuta-msft]

@Anonymous ,

Could you show more details about the performed steps? For example, have you done some operations on the excel tables,  like merge/append a excel table with table in sql server in direct query mode. 

Regards,

Jimmy Tao

[Anonymous]

I was using the same queries and steps that are created when you combine multiple xlsx files together - except doing it for multiple worksheets in the same file. (So having the sample work sheet, and a function that runs on each of those worksheets to apply any modifications per sheet). Then I expand all the worksheets into one table.

I also had one other xlsx source, that I then append onto the end.

Regardless of the steps, there is definately a lot of unessecary overhead when Power BI loads xlsx files. When processing the load, Power BI will reload the xlsx file multiple times. Also the speed that it loads say a 1mb or 2mb is very slow (like watching it at 1kB a second or something). 

Just knowing that doing the equivilant data load and manipulation in python, using pandas, would load the file in an instant, and perform the data manipulation shortly after - a fraction of the time. (I would just use that here, but Python isn't available on report server).

[J_Twice]

Four years later and the situation is the same 😞 Working with excel files as a datasource is terrible slow - as you can see e.g. in ProcMon tool, Power BI (resp. Mashup service) reads the excel file again and again and again and everytime in slow way 😞

PS: The workarround is to export Excel data to CSV as Chris Webb wrote in his blog.