Recently we faced the issue on one project to refresh power BI desktop file to update structures.
There could be various reasons why you would like to automate PB desktop data refresh. For example:
- You would like to update table structures. (when you have a simple connection in power query to SQL server Table/View. You can even change/add/remove column. After refresh it’s updated in PB file.)
- You would like to create PB desktop testing as a part of your CI pipeline before it’s deployed to PB Workspace.
- You want to remove/change data from PB and you cannot use PB template
However, PB desktop is not really designed for data-refresh automation. You can find lot of articles about how to refresh dataset in the service but not many how to refresh it in the PB desktop.
Here is very old article providing some options, out of which most do not work nowadays for PB desktop. The most promising one was this Powershell script from Michal Dubravcik. This is a PowerShell script that opens Power BI Desktop and sends a key to the Refresh button.
However even this script did not work on Windows 10. Michal made a new version in Pyton but I had no knowledge of Python and no option to setup Pyton on the server just because of this.
So, I decided I’ll rework Powershell script for current version of Windows.
Important note: This is not officially supported way to do it from Microsoft and it can stop working any time. No guarantee or support is provided from my side. Take this just as an inspiration for your needs.
Script is commented inside so I do not think there is much to add here but what the script does:
- Kill PB if it’s opened with new file name.
- Copy pbix workbook under new name.
- Open PB Desktop with new file and wait until it’s open.
- Hit the refresh button.
- Wait until refresh is finished.
- Hit the Save button.
- Close PB Desktop
If you download the demo, put all files into one folder and you should be able to run ps script and it will create new Demo_New.pbix file and refresh it. Demo file is connected to website with time so you should see in new file different time then in the old one without refresh.
You can get script from here. Enjoy!
I have around 20 years of experience with MS data platform. In last couple years I’m passionate mainly about PB/SSAS modeling and DAX. Proud father of three boys who teach me to live 🙂