Generate PowerBI friendly Excel Tables in Python

by

Recently i had a project which had me automate a bunch of excel files and serve them up to Microsoft’s Power BI through an on-premise Gateway.

The source Excel files were a pretty big mess, and needed quite a bit of data massaging before being ready. I had thought it was all good-to-go using openpyxl, however while it looked fine, Power BI refused to use it correctly as a data source.

I played aorund with encoding and still had no luck until i tried it in PowerShelll, and it worked splendidly. Not wanting to re-write the entire process. i used the following functions within the python script to generate a temporary powershell script and execute it.

I have generalized the code, and am posting it here in case its of use to anyone. 

# This Python Script Generates Power BI Friendly Excel Tables in Excel Wroksheets using PowerShell
import os
import subprocess, sys

# TODO Script Variables
path = "" #path to use for generated PowerShell Script
pshell_file = "example.ps1" # name of PowerShell file to Generate
pshell_modules = ['ImportExcel'] #List of Modules to Import in PS Script
pshell_name = "" #Name of the temporary PowerShell file to Generate and Run within Python
excel_file = "" #Name of the Excel File to insert the taables to
sheet_name = "example_worksheet" #Name of the Excel Sheet to address
table_name = "example_tablename" #Name of the new Excel Table 

# TODO Function that Executes the Created PowerShell Script
def execute_script(script):
    cmd = ["powershell","-ExecutionPolicy", "Bypass", f"{path}\{0}.ps1".format(script)]
    p = subprocess.Popen(cmd, stdout = subprocess.PIPE, stderr=subprocess.PIPE, stdin=subprocess.PIPE)
    out,err = p.communicate()

    if(err):
        raise Exception('Error: ' + str(err))
    return out

# TODO Main Function to Generate Tables
def gen_excel_tables(fn, modules, excel_file, sheet, table):
    fn = pshell_file
    os.remove(fn) if os.path.exists(fn) else None
    with open(f'{pshell_file}', mode='x') as file:
        for m in modules:
            file.write(f"{fn} -Name {m}\n")
        file.write(f"Import-Excel -path 'C:\{path}\{excel_file}' -WorksheetName '{sheet}' | Export-Excel -Path 'C:\{path}\{excel_file} -WorksheetName '{sheet}' -TableName '{table}' -AutoSize\n")
        file.close()
        execute_script(f"{pshell_file}")

# TODO Usage 
gen_excel_tables(fn=pshell_file, modules=pshell_modules, excel_file=excel_file, sheet=sheet_name, table=table_name)