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
wyn.bio.link/
Access Analytic blog: accessanalytic...

[@wayneedmondson1065]

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!!

[@AccessAnalytic]

Cheers Wayne

[@EricHartwigExcelConsulting]

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

[@AccessAnalytic]

You’re welcome Eric, it’s still a problem I see a few times a year.

[@scottcha03]

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

[@AccessAnalytic]

You’re welcome, odd that it worked with file open

[@ExcelOffTheGrid]

Another great video Wyn. It’s an important message to share. I wonder if .xls will ever die as a file format.

[@AccessAnalytic]

Cheers, one day maybe. A year or so ago I came across a company still running on Lotus123, so the legacy tail will be very long.

[@bjorns1039]

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

[@AccessAnalytic]

Thanks Björn, tell all your friends 😆

[@arkd3um1988]

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

[@AccessAnalytic]

Glad to help

[@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.

[@AccessAnalytic]

Yep, it's scary that it's still a thing

[@cesarmr111]

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.

[@AccessAnalytic]

There are some ways powerautomate.microsoft.com/en-us/templates/details/8925727cb4f044e9b577f5ae111f2947/convert-xls-files-to-xlsx-using-ui-flows/ eriksvensen.wordpress.com/2020/06/15/converting-xls-files-to-xlsx-file-using-powerautomate-and-avoid-the-pitfalls-in-powerquery-using-xls/

[@cesarmr111]

@@AccessAnalytic Thank you very much. I will try

[@notesfromleisa-land]

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

[@AccessAnalytic]

😆

[@realpulsecoin]

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

[@AccessAnalytic]

No

[@realpulsecoin]

@@AccessAnalytic Thank god. And thank for your great content!

[@JJ_TheGreat]

@@AccessAnalytic What about csv files?

[@AccessAnalytic]

Csv are the best file type to use

[@IvanCortinas_ES]

Wise advice Wyn! Thanks for sharing it.

[@AccessAnalytic]

Cheers Iván

[@ramakantjoshi6551]

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

[@AccessAnalytic]

You are welcome. Thanks for taking the time to leave a kind comment

[@andrewcharlesmoss]

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

[@AccessAnalytic]

Totally

[@GrainneDuggan_Excel]

That is truly scary! Thanks for the warning

[@AccessAnalytic]

No worries Grainne

[@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

[@AccessAnalytic]

I have heard of issues but never tried myself

[@Shalinee13]

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

[@AccessAnalytic]

You’re welcome

[@bulbulahmed3098]

Excellent. From Bangladesh

[@AccessAnalytic]

Thank you Bulbul

[@malejandrahorvath]

Wow, thanks for advice! Loved the thumbnail though 😉

[@AccessAnalytic]

Hah! Thanks Alejandra

[@ziggle314]

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.

[@AccessAnalytic]

There’s another option powerautomate.microsoft.com/en-us/templates/details/8925727cb4f044e9b577f5ae111f2947/convert-xls-files-to-xlsx-using-ui-flows/ eriksvensen.wordpress.com/2020/06/15/converting-xls-files-to-xlsx-file-using-powerautomate-and-avoid-the-pitfalls-in-powerquery-using-xls/

[@ziggle314]

@@AccessAnalytic This is a great suggestion! Thanks Wyn.

[@AccessAnalytic]

No worries

[@randomguy-jo1vq]

Please tell me how can I convert multiple xls to xlsx without opening files either manually or through vba.

[@AccessAnalytic]

See my power automate link in the earlier comment

[@cm00000]

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

[@AccessAnalytic]

I don’t think those are supported

[@AccessAnalytic]

community.fabric.microsoft.com/t5/Service/Excel-xlsb-file-type-Refresh-Fails/m-p/2499907#M159382

[@brianspiller9075]

Convert command is on my QAT

[@AccessAnalytic]

Good one Brian

[@z.719]

Great content 👍 thanks for sharing

[@AccessAnalytic]

You’re welcome

[@7213261]

Thanks!

[@AccessAnalytic]

You’re welcome Sergey

[@GeertDelmulle]

Thanks for the warning! Will propagate. :-)

[@AccessAnalytic]

Thanks Geert

[@fennecfennec5234]

thank you

[@AccessAnalytic]

You're welcome