How to Automatically Update Power BI Reports?

Learn how you can use a PowerShell script to automatically refresh the data for the end-user in a Power BI report.

At one of our clients -that was previously using a solution that includes a limited set of the Acterys functionality- we saw that they had licensed a separate update solution that refreshes the underlying Power Pivot model in an Excel workbook that is used in a Power BI report. This is a requirement that can easily be implemented in a few minutes using a Windows PowerShell script in conjunction with the Windows Task Scheduler. In the following steps I describe the process:

Create The PowerShell Script

Let’s create the PowerShell script to automatically update Power BI report from Excel workbook.

1. Open Windows PowerShell
2. Copy and paste the following code and replace “Yourpath\yourExcelworkbookname.xlsx” with the details of your workbook:

#Set the file path (can be a network location)
$filePath = "Yourpath\yourExcelworkbookname.xlsx"
#Create the Excel Object
$excelObj = New-Object -Com Excel.Application
#Wait for 10 seconds then update the spreadsheet
Start-Sleep -s 10
#Make Excel visible. Set to $false if you want this done in the background
$excelObj.Visible = $true
$excelObj.DisplayAlerts = $false
#Open the workbook
$workBook = $excelObj.Workbooks.Open($filePath)
#Wait for 10 seconds then update the spreadsheet
Start-Sleep -s 10
#Refresh all data in this workbook
$workBook.RefreshAll()
Start-Sleep -s 10
#Save any changes done by the refresh
$workBook.Save()
$workBook.Close()
#Uncomment this line if you want Excel to close on its own
$excelObj.Quit()
$excelObj = $null
write-host "Finished updating the spreadsheet" -foregroundcolor "green"
Start-Sleep -s 5

This script will open the respective workbook run the refresh for the data models, save and close the workbook. This means that if you have a Power BI report that is using this workbook, you can just refresh the Power BI report and will get the updated data.

Setup The Update Interval

This process can be scheduled so that it runs completely automatic in a specific interval.

1. Open Windows Task Scheduler:

2. Create a new task:

To configure the interval go to the “Triggers” tab and:

3. Click on new:

Her you can now:

4. Setup the trigger and specify the update interval. For example, every day at 12:54:

The final step is to specify what action you want to run. To do this:

5. Go to the “Actions” tab and choose: “Start a program” and point to the location of your script:

That’s it. This can obviously be configured and refined with all the options that PowerShell gives you respectively be integrated into a standalone app which is available in Acterys for an even easier usability and with additional features. Please don’t hesitate to contact us for any further questions.

Why Automatically Update Power BI Reports?

When you add the PowerShell script provided above, data in your Power BI reports will automatically get updated from Excel workbooks. This means that you don’t have to worry about manually refreshing your reports. The updates occur in the background, so you can continue working while your data is being refreshed.

This is a great workaround because it ensures that you always have the most up-to-date information from Excel workbooks directly replicated on Power BI reports. Additionally, it eliminates the need to manually refresh reports, which can be a time-consuming process.

Can You Automatically Update Power BI Reports from Excel Workbook?

In modern versions of Power BI, you can automatically update Power BI reports from an Excel workbook if you set up a Power BI dataset and schedule a refresh.

To set up a dataset, open the Power BI service and select Get Data. Select Excel, then choose the workbook you want to use.

To schedule a refresh, open the workbook, select Refresh Now under the Home tab, then select Schedule Refresh. In the Schedule Refresh dialog box, select the frequency and time of day you want the refresh to run, then select OK.

If you don’t want to go through all this hassle, then Acterys completely automates Power BI reporting updates from any source. Learn more about Acterys by contacting us today.

Key Takeaways

1. Data refresh process for Power BI reports from an Excel workbook can be done using a PowerShell script, saving time and ensuring up-to-date information.
2. To set up the automation, create a PowerShell script that opens the Excel workbook, refreshes the data models, saves changes, and closes the workbook automatically.
3. Automating Power BI report updates from Excel workbooks eliminates the need for manual refreshes, saving time and ensuring data accuracy.
4. While PowerShell scripting is an effective method, Acterys offers a streamlined and user-friendly solution for automating Power BI reporting updates from various sources. Contact Acterys for more information on this alternative approach.

FAQs

1. What is the process for automatically updating Power BI reports from Excel workbooks using PowerShell?

To automate the updating of Power BI reports from Excel workbooks using PowerShell, you can follow these steps:

Create a PowerShell script that opens the Excel workbook, refreshes the data models, saves changes, and closes the workbook automatically.

Schedule the PowerShell script to run at specific intervals using the Windows Task Scheduler.

2. Why should I consider automatically updating Power BI reports?

Automatically updating Power BI reports ensures that your reports always contain the most current data from Excel workbooks without manual intervention. This background update process saves time and allows you to work on other tasks while your reports stay up-to-date.

3. Can I automatically update Power BI reports from an Excel workbook without using PowerShell?

Yes, in modern versions of Power BI, you can set up automatic updates for Power BI reports from Excel workbooks by creating a Power BI dataset and scheduling a refresh. Simply open the Power BI service, select “Get Data,” choose the Excel workbook, and set up the refresh schedule. Alternatively, you can explore Acterys, which provides a streamlined solution for automating Power BI reporting updates from various sources.