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

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.

Limitations

  • 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.
On-Premises data gateway error without Personal Mode

You may also like...

8 Responses

  1. ntc33 register says:

    I really like reading through an article that can make people
    think. Also, thanks for allowing me to comment!

  2. Pooja says:

    Very informative article. Is there any way to email the report instead of saving in the local system?

    • Jesse says:

      I have not actually tried to send an email with this method. However, it should be possible to send an email with R.

  3. Sam says:

    Thanks this is helpful. How can I avoid exporting column names? Also, the R script is exporting first column as sequential number. How do I avoid exporting the sequential number column as well? Any help is appreciated.

  4. Sam says:

    Thank you this is helpful. How do I avoid exporting column names using R Script? I am new to R and I am not sure how this can be done. Also It is exporting the first column as sequential number which I want to avoid. .

    • Jesse says:

      Hello Sam, if you are exporting to a csv this can be achieved with the following code:

      write.table(dataset, file = “C:/dataset.csv”,sep=”,”, row.names=FALSE,col.names=FALSE)

      Note that here I use the write.table function as it has more parameters to control the data.

      I hope this helps you towards your solution.

  5. Bernd says:

    Hi Jesse, thank you for the very detailled article. Is it anyhow possible to export automatically besides the personal mode? Thanks in advance.

Leave a Reply

Your email address will not be published. Required fields are marked *