In this blog we will see how to automatically refresh data in Power BI that is acquired via an Excel-based Microsoft Query.
This came about from a requirement to analyse data from Sage Line 50, which Power BI does not currently connect to directly, but the method can equally be applied to any other data source which an Excel-based connection does support but Power BI doesn’t.
The method uses a simple Powershell script and basic Windows task scheduling to automate the data load into the spreadsheet
Step One
The first step is to create the Microsoft Query from within Excel:
This blog isn’t about that though, it’s about automating it (see this blog for creating the query). I used SQL Server to test the concept as I don’t have access to Sage Line 50 here. I just created a test database and two-column table into which I inserted rows as required for testing. Again, plenty of information available on how to do that.
Step Two
2. Create a Powershell script to open the file, refresh the data connection, wait for long enough to compete the refresh, then save and close the file.
$dir="C:\Test\MsftQuery3.xlsx"
$excl=New-Object -ComObject "Excel.Application"
$wrkb=$excl.Workbooks.Open($dir)
$excl.DisplayAlerts = $FALSE
$wrkb.RefreshAll()
Start-Sleep -s 5
$wrkb.Save()
$wrkb.Close()
$excl.Quit()
You can create the file in Notepad or other text editor, then save it as .ps1 so it becomes a Powershell script.
Tip: I had problems with the Windows scheduler when I had a long file path, so for this example have created a folder on the root of C.
Step Three
Schedule the script to run with the Windows Task Scheduler.
You can use the Create Basic Task wizard or the Create Task dialogue. Here using the Create Task dialogue:
a. A network (not system) user will be required. To run when the user is not logged (which is what you will want) on it needs the Log on as a Batch Job right. My user doesn’t have this so for this test I’ve set it to run when I’m logged on.
b. Set the trigger to run when as required:
c. The action is to open the Powershell programme with an argument of your specific file (you can see the full path to my file in the background window of the screenshot below.
You can test it by selecting it then clicking Run Now from the Actions menu:
Step Four
That is the data automated into the Excel file, so you just need to create & publish a Power BI report and automate the refresh from the Excel into the published report’s dataset. I won’t describe in detail as it’s all in the standard documentation, but the basic steps are:
a. Create report in Power BI Desktop against the Excel file
b. Publish report to Power BI
c. Create a Gateway
d. Add to the Gateway a Data Source which connects to the Excel file
e. Connect your published report’s Dataset to the Gateway
f. Schedule the refresh of the Dataset