Very slow when using data from multiple Excel files from folder

[sqsjoshi]

Hello,

have just installed latest version of Power BI and am finding Power BI to be extremely slow. As a test, trying to combine data from 5 Excel files with 3 worksheets each (25 columns), max of 65K rows on each worksheet. It is taking 15+ mins to detect all worksheets and now for 20+ mins its "evaluating query". I have done no transformations at all. Using AWS EC2 machine, Xeon Platinum CPU @ 2.90GHz / 32 GB RAM.

Are you aware of some issue with AWS machines? Is working with Excel files (rather than text) really slow for some reason? I expect to work with 1-5M datasets as text files and 500-750K line Excel files, generally 3 worksheets per file. At t his rate, there is no way I can use Power BI so want to make sure I am not missing something. Thanks in advance for help.

[Shosher]

It might be the internal storage speed that gets dedicated from AWS to find those local files. This can often be slow. You can try creating a dataflow to do exactly what you do now but from the browser. then use that dataflow as your source in the report. possibly it takes the weight off your local machines shoulders. my tables with 100M rows evaluate in seconds. 

[sqsjoshi]

Shosher, the files are available locally on the machine. BTW, I saved all worksheets as .csv files and yes, they link readily and I can transform.

BTW, have you tried to link any Excel files or do you normally work with only text/csv files?

[OwenAuger]

Hello @sqsjoshi 

Just checking, what Excel file formats are you connecting to?

Connection to the .xls and .xlsb "legacy" formats tends to be significantly slower (among other issues) as they use the Access Database Engine OLEDB (see here).

I would generally recommend .xlsx or .xlsm for this reason.

[sqsjoshi]

OwenAuger,

using .xlsx files. Pl see my reply above - csv files work. There is still issue but hoping to overcome that.

Looks like I need to stay away from Excel files. That would be a bummer.