Beware using .XLS files as a Power Query data source

My explanation of why you shouldn't use old .xls format Excel files as a data source for Power Query

Connect with me

[EricHartwigConsulting]

Thank you Wyn for this great video and the full explanation on why this is a problem and what causes it.

[bjorns]

Wow! Again you highlight some very important things that isn’t easy to find out! You are really awesome!!

[wayneedmondson]

Hi Wyn. Thanks for the great advice! I've also found that if you are pulling data from many .xls files, particularly those with multiple worksheets in each file, then queries run much more slowly than if you convert all those same files to .xlsx. Just another reason to avoid connecting to .xls. Thanks and thumbs up!!

[ExcelOffTheGrid]

Another great video Wyn.

It’s an important message to share.

I wonder if .xls will ever die as a file format.

[arkdum]

small details are sometimes so damn important... thanx. i did had such issues... but since im new to power query, i though i was doing something wrong

[scottcha]

Just ran into this issue. Underlying number not pulling over unless I have the xls file open. Thank you for confirming and for sharing.

[IvanCortinas_ES]

Wise advice Wyn!
Thanks for sharing it.

[leerv.]

XLS is sneaky in so many ways, it really is a different format altogether. I was working on some ad hoc query a dev sent me and there was so much data missing, it looked like the source DB had a major issue, until I noticed it was exported to me in XLS format, which of course has a row limit of 65536, which explained the problem. But not everyone is aware of this, and people could have taken issue escalation actions thinking the DB was faulted. Crazy to still see the format in 2022, and in large companies. ...Actually, scratch that, it seems like the larger companies have more legacy, big and slow to adapt.

[ramakantjoshi]

Good one.... i was also having same problem... Thanks for your help. God Bless you.

[notesfromleisa-land]

This cautionary tale in the Book of Wyn in the Excel Bible is important!

[Shalinee]

Thanks ...this video is life saver for me.

[GrainneDuggan_Excel]

That is truly scary! Thanks for the warning

[andrewcharlesmoss]

It worries me that XLS files are still in use. 😬

[malejandrahorvath]

Wow, thanks for advice! Loved the thumbnail though 😉

[iankr]

Hi Wyn
Many thanks for this. I've heard that you should also avoid the current binary format .xlsb files as the source for Power Query queries. I prefer these over .xlsm files for macro-enabled files, as they have a much smaller file size. But I've been warned off them when working with Power Query. Have you heard this?
Thanks

[ziggle]

For me, XLS has been an endless source of data errors that have been rather difficult to find. Unfortunately, I mainly work with data gathered by old automated test platforms (e.g. Arbin battery testers) that only generate XLS format files. These files always have issues. I ended up creating a VBA routine that converts all my XLS files into XLSX before I process the files with Power Query. While this works, it means that I have not been able to stop using VBA, which is frowned upon by the security policies of many companies.

[cesarmr]

Thank you very much!. I have one trouble. I am tryingt to import xls files from web, but when they charge in power query some cells are blank. To recover the information I would need to convert manually the xls to xlsx. My question is: is there any method to convert the web xls file to xlsx using powerquery/ powebi?. Thanks.

[realpulsecoin]

Great! Have you seen this faults from other sources? Like txt, or PDF?

[GeertDelmulle]

Thanks for the warning! Will propagate. :-)

[cm]

How about if i have xlsb files as datasource. Im having issue scheduling an autorefresh in power bi service if the files are xlsb