I recently had this issue where I needed to access a .xls spreadsheet with Power BI. It turns out if you have the 64 bit version of Power BI, Power Query will not be able to connect to .xls Excel spreadsheets.
You'll get an error message like this:
DataSource.NotFound: 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 may be required to read 'My Workbook.xls'. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.
Rather than downloading the 32 bit version of Power BI just for this project, I figured I could just use Python to connect to the spreadsheet. This can be a simpler approach if you already have Python installed, and don't want to have two versions of Power BI. For the example below, I'm using Python 2.7.
If you are not sure how to enable Python in Power BI, check out my other article here.
I used the Pandas library to do my importing. It's a very simple script that can be referenced here: https://pythonspot.com/read-excel-with-pandas/
The one thing I changed is to use 'r' in front of the file name. This insures the file name only includes valid string characters. Otherwise, you may get a 'File not found' exception (error).
import pandas as pd from pandas import ExcelWriter from pandas import ExcelFile fileName = r"My Workbook.xls" df = pd.read_excel(fileName)
Load this script into the Python data source in Power Query and you are ready to go!
Passing a Variable Into the Python Script
In order to make this a dynamic script, we'll need to modify the M script a bit. Let's say we want to pass in any file path for the Python code. We just need a couple &'s and "s to do this. This is what our current M script looks like:
let Source = Python.Execute(" #(lf)import pandas as pd #(lf)from pandas import ExcelWriter #(lf)from pandas import ExcelFile #(lf)#(lf)fileName = r""My Workbook.xls"" #(lf)df = pd.read_excel(fileName) "), df1 = Source{[Name="df"]}[Value] in df1
We can create a variable varFileName for the M script. We will use this to pass into the Python script. We just need to replace ""My Workbook.xls"" with """ & varFileName & """. Notice the additional double quotes before and after the &. The double quotes on either side went from 2 to 3. Our resulting M code looks like this:
(varFileName as text) as table => let Source = Python.Execute(" #(lf)import pandas as pd #(lf)from pandas import ExcelWriter #(lf)from pandas import ExcelFile #(lf)#(lf)fileName = r""" & varFileName & """ #(lf)df = pd.read_excel(fileName) "), df1 = Source{[Name="df"]}[Value] in df1
One slightly annoying thing I found out was that, if you apply this function to multiple queries, you'll need to grant permission for each query in order to run the Python script. But, other than that, I think it's a nice way to avoid two versions of Power BI.
If you are using R scripts, the same method can be applied. Check out Stacia's blog post here.
Let me know what you think! If you have any other Python & Power BI tricks I'd be happy to hear!
Building an Access Database? Check out my ebook!
