Power BI: Creating a Function with Python

August 22, 2018

 

 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!

 

 

Please reload

About the Author

Brent Jones loves spending time with his wife and trying out new activities together.  They traveled as digital nomads to many countries in South America, Europe and Asia. He can also speak Japanese!

Our Recent Posts

Please reload

Tags

Address

7000 Central Pkwy #1100, Atlanta GA 30328

Follow

  • Twitter - Black Circle
  • Black Pinterest Icon

©2019 BY ANTARES ANLYTICS. PROUDLY CREATED WITH WIX.COM