Export data automatically from Power BI to CSV/Excel

Schedule data exports from Power BI via R script

Sometimes we need to export data from Power BI for usage statistics, performance or to track changes in data after each refresh.

In this post, I will export my data after transforming, cleaning and preparing them in Power Query, via an R script.

🚩Note that the export will be performed each time the dataset is refreshed, which means that DAX-calculated columns added later in the data model will not be included in the exported data.

Steps:

• Within Power Query Editor, after having transformed the data, click Transform=>Run R script

🚩You must have R beforehand installed on your local machine

✔Export to Excel file:

Run the following R Script 👇after having install “writexl” package in your R environment:

👉the outcome:

✔Export to CSV File:

👉the outcome:

Configuring the Gateway & the Data Privacy level in Power BI Service:

After publishing the report in the PowerBI.com, go to the setting page of the concerned dataset:

1. Set the Privacy Level to public

   

   2. Configure and specify your On-premises data gateway (personal mode)

3. Setup a scheduled refresh of the dataset, so that your data will be exported at each refresh according to the frequency of the refresh you have configured

===>Note that there are several approaches to do this workload , such as with Power Automate flows.