How to Automate Power BI Data to CSV with Power Automate and SharePoint

In my experience working with Power BI and Power Automate, automating data exports is a crucial part of efficient data management. In this tutorial, I’ll guide you through a simple, scalable process for exporting Power BI data to a CSV file and saving it to a SharePoint library daily. This approach ensures you maintain a log of your Power BI report changes in SharePoint, without manual intervention.

Why Automate Power BI Exports?

Keeping track of daily changes in your Power BI reports is essential, especially if you want to monitor how your data evolves over time. Automating the export process removes the need for manual updates and ensures that your data is always up-to-date. Using Power Automate, you can schedule this task to run on a daily, weekly, or even hourly basis.

Step-by-Step Process

1. Prepare Your Power BI Report Start by filtering your Power BI report to focus on the key metrics you want to track. In my case, I filtered sales data to show only sales from Europe and North America and removed the total summary to focus solely on these regions.
2. Create a Scheduled Flow in Power Automate Next, head over to Power Automate and create a scheduled cloud flow. You can set the flow to run at a specific time each day. In this example, I scheduled the flow to run at 10 a.m. daily.
3. Connect Power Automate to Power BI Add the “Run a Query against Power BI Dataset” action to pull the data from your Power BI report. You’ll need to generate the necessary query from your Power BI report by using the “Performance Analyzer” in Power BI Desktop.
4. Capture and Parse the Data After running the query in Power Automate, capture the JSON output. This output will form the basis of the CSV file. Use the "Parse JSON" action to structure the data properly.
5. Create and Save the CSV File in SharePoint With your data ready, the next step is to create a CSV file. Use the “Create CSV Table” action in Power Automate, to ensure that the CSV format is correct. Finally, add the “Create File” action to save the CSV file in your desired SharePoint document library. Make sure to use a dynamic filename (e.g., including the date) to avoid overwriting previous files.
6. Test and Monitor After setting up the flow, test it by running it manually to ensure everything works as expected. You’ll find your CSV file neatly stored in SharePoint, with all the Power BI report data accurately logged.

Why This Automation Matters

This automated process not only saves time but also reduces human error. By scheduling this task, you ensure consistency and avoid the risk of missing crucial data updates. Plus, having all your Power BI report data saved in SharePoint makes it easily accessible and shareable across teams.

Final Thoughts

Automation is key in today’s fast-paced, data-driven world. By leveraging Power Automate and SharePoint together, you can streamline your workflows and focus on what matters most: analysing your data. This method will help you build a robust system for managing and logging Power BI data effortlessly.

For a detailed walkthrough, watch the full video tutorial: Automating Power BI to CSV with Power Automate.