Automatically update Power BI Desktop in Python

Power BI is convenient, isn't it?

If you make it once, it feels good that monthly analysis is done automatically. Clarity that allows you to freely arrange graphs and understand the desired data at a glance. Filters and hint functions that allow you to deepen your analysis just by operating the mouse. Relationships between tables that can be connected intuitively. Cooperation with various data sources. After all, I'm surprised that it's free.

I also use it to analyze hundreds of millions of data. It's fun to use the hint function to show what was happening at that time. However, there is one problem.

It's annoying to update! !! Even though the data is preprocessed, it takes tens of minutes to read the data. It's stressful when you want to get it out. The PC becomes heavy. Besides, if you try to update on a PC with low specifications, it will fall due to lack of memory. It seems that you can automatically update with unbranded Power BI instead of Power BI Desktop, but it will be charged and account management is difficult.

So, this time I made a program in Python that automates Power BI Desk Top.

Environment / configuration

I use the Windows 10 Pro Task Scheduler to run Python programs on a regular basis. I use Pywinauto to open the pbix file in the GUI, click the "Update" button and then save it. By the way, the data source is obtained by connecting to MySQL on the server.

code

auto_pbi.py

import os
import sys
import time

from pywinauto import Desktop, Application, keyboard

def main(workbook):
    exe = 'PBIDesktop.exe'

    #Open file
    os.system('start "" "{0}"'.format(workbook))
    app = Application(backend='uia').connect(path=exe)
    time.sleep(60)
    try:
        #Specify Window
        win = app.window(title_re = '.*Power BI Desktop')
        win.set_focus()
        #Click Home> Update
        win.home.wait("visible")
        win.home.click_input()
        win.update.wait("visible")
        win.update.click_input()
        win.Cancel.wait_not("visible",timeout=6000)
        #Save
        keyboard.send_keys("^s")
        time.sleep(120)
    except Exception as e:
        print(e)
    finally:
        app.kill()


if __name__ == '__main__':
    try:
        file_path = sys.argv[1]
    except (IndexError):
        print('Please specify the file.')
        sys.exit()

    main(file_path)

The above code is specified in auto_pbi.bat and it is run in the task scheduler.

auto_pbi.bat

cd specified folder
python.exe auto_pbi.py report.pbix
exit

For Python, .py files, and pbix files, it is recommended to write them with absolute paths. I have put the report and py files in the folder, so I moved the folder on the first line.

important point

Run task scheduler

I tried to run this program on a remote PC but it didn't work. If I "run" the task scheduler myself, it works, but if I disconnect the remote connection and try to run it with a trigger, it doesn't work. This is because with a remote PC, the desktop screen is locked when the remote desktop is disconnected. I get the following error:

there is no active desktop required for moving mouse cursor!

To take this measure, you need to set the desktop screen so that it does not lock out when you turn off the remote desktop. https://pywinauto.readthedocs.io/en/latest/remote_execution.html

I took the method of using the following command when turning off the remote desktop. Please change the place where \ # 80 is according to the environment. Open Task Manager> User on the remote PC, right-click the heading of each column and check "Session" to display the remote session ID. Enter the following command from the command prompt to disconnect the remote desktop session.

TSCON RDP-Tcp#80 /dest:console

Sleep processing

If I didn't wait for a while after launching pbi, an error occurred because Window did not launch. Also, I thought that I should be careful unexpectedly, the sleep processing after saving with Ctrl + S. I thought I could complete the save process in about 30 seconds, but I couldn't. I think it's better to keep it longer than you think.

Other

If you can copy the file after updating and send it, or if you get an error, send an email, etc., if you can base it on this, you can save the troublesome processing such as daily big data update and send a comfortable Power BI life. I think you can get it!