How to read XLS in to Power BI

Excel Workbook: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine 2010 Access Database Engine OLEDB provider is required to connect to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987

It looks like you tried to open a XLS file with Power BI. Yeah, no. XLS is forbidden territory, you can't just walk in there. You have to be sneaky.

Here's the trick:

Source.xls --> Excel-file located in OneDrive --> Power BI

Did you see it? It's right there, in between. Let's zoom in:

XLSX-file located in OneDrive (or any other cloud storage for that matter) that imports the XLS file using Power Query

It's really that simple.

1. Locate the XLS file you wish to use.
2. Import it into a cloud stored XLSX format Excel-file. Use desktop Excel to create the connection with Power Query.
1. Read the XLS as Web.Contents and then as Excel.Workbook, like this: Excel.Workbook(Web.Contents("http://the.locationofthefile.com/wholepath/theXLSfileyouneed.xls"), null, true)
3. Do the necessary magic to prepare the data
4. Save your newly created XLSX file.
5. Get the location of the file just created from the desktop Excel.
6. Read the Excel file to Power BI:
1.  Excel.Workbook(Web.Contents("http://your.cloudstoragelocation.com/wholepath/thenewexcelfile.xlsx"), null, true)
7. Use it as you wish in your Power BI report/dataset/whatever.

Of course this is bogus if you are meant to handle a load of XLS-files. Ain't nobody got time to go through all the files individually.

In the case of multiple XLS files the best solution is to make the intern convert all the files into XLSX-files manually, and use the following interns to keep the files up to date. Also quitting your job and starting a career as a sheep farmer in New Zealand is a valid option.