How to Auto Schedule Data Export From Power BI
There always comes a time when you would like to export data whether it is usage statistics, performance or other data you would like to feed back into the BI system. In this post, you will see how to perform an automated data export using Power BI.
Step by Step
1. Open up Power BI Desktop and connect/open your data source you would like to export.
2. Click on the Edit Queries of the Data table you would like to export.
3. Once inside the Power Query Editor click on the Transform tab, and then Run R Script.
At this moment the Run R Script window will open where you can write R scripts which we will use to export the data. Therefore, in order to run R scripts, R should be installed first.
Installing R & Setup R
For this example, I will use Microsoft R Open.
1. Install R if not done so already. The installation link can be found from Power BI or alternatively here.
2. Once R has been installed open the Run R Script once again and paste the below R script, depending on your destination and save it.
For simplicity, exporting to CSV will be used in this post. You can, of course, export to any destination of your choice, e.g. SQL Server, Excel, SAS or Oracle.
Export to CSV
write.csv(dataset, file = "C:/dataset.csv")
Setting Privacy Level for R Scripts
To be able to successfully run an R script the correct privacy levels have to be set up first.
1. Click on File > Options and Settings > Data source settings
2. For each data source using the R script, Public privacy level should be selected. This is done by clicking on Edit Permissions > Select Public in the dropdown.
Publishing Power BI Workbook
1. Once you have written your R script and set up correct privacy settings. Save the Power BI workbook and Click Publish.
2. Open Power BI in the browser and find your newly published Dataset inside the workspace you published to. Then click on Schedule Refresh.
In order to schedule an automatic refresh, a data gateway has to be installed. In addition, only the On-premises data gateway (personal mode) supports the execution of R scripts.
Installing the Data Gateway
1. Download and install the Power BI Data Gateway.
2. Proceed with the installation of the Data Gateway, it is important to install the On-premises data gateway (personal mode) for the scheduling to work.
3. Enter the email which will be used for the Gateway.
Setting up Automatic Schedule Refresh
Once you have installed the On-premises data gateway (personal mode), you should now be able to see the Personal Gateway available in Power BI under Gateway connection.
1. Toggle the Use a data gateway to On.
2. Expand Data source credentials and update it so that the warning message disappears.
3. Expand Scheduled Refresh, and select a refresh frequency of your choice and Apply.
Once all the above steps have been completed, Power BI will download the data to your target destination automatically.
- R data source settings have to be set to public as well as all other steps created in the Query Editor.
- To enable a scheduled refresh of R scripts, On-premises data gateway (personal mode) has to be used.