Looking to automate data snapshots in Power BI or data source with regular exports? In this blog, we'll dive into the seamless integration of automatically exporting data snapshots through Power Automate flows within the Power BI online service.
Imagine you're consistently updating a deposit fund activity Excel spreadsheet in a SharePoint Document library whenever funds are deposited. You wanted to have a csv file with the deposit fund data on a daily, weekly or monthly basis. Instead of manually downloading or copying and renaming the excel file daily. We will discuss on how you can automate this process.
Within the Power BI online service, the deposit fund activities are consistently loaded and published. Our focus lies on a singular visual table, showcasing essential details like date, reference number, activity name, and fund amount. We wanted to export a data daily into a csv file and save it in a SharePoint document library.
In this demonstration, our tool of choice is Power Automate, facilitating the creation of CSV file formats to be stored within a designated folder in the SharePoint document library.
Our initial step involves creating a folder within the library where all extracts will reside. For simplicity, let's name this folder "My Deposit Fund Snapshots," ensuring it's stored alongside the Excel file.
Next, launch Power Automate and initiate the creation of a new flow. Given our need for daily data snapshots, we'll opt for the scheduled flow type to ensure timely and automated updates.
If you're not yet familiar with the process of creating a Power Automate flow, simply click the "Skip" button. This action will redirect you to the flow designer page. From there, navigate to the search bar and input "scheduled," then select "Recurrence" from the search results.
Once you've selected the "Recurrence" trigger, proceed to configure it according to your desired trigger frequency. In our example, adjust the interval to "1" (indicating how many times the flow will run), set the frequency to "Day" (determining how often the flow will be triggered), and specify the time zone along with the specific time for execution.
Then, add a new action Current Time.
Following the trigger configuration, add an action named "Run a query against a Dataset." Provide the workspace name where your Power BI report is saved. In our example, the "Deposit fund report" is stored solely in "My Workspace."
After that, specify the dataset name. Typically, the dataset name mirrors that of the published Power BI report.
To retrieve the Query Text, navigate to the Power BI Desktop. Click on the "View" menu and select "Performance Analyzer" from the options provided.
Make sure you have selected the table before anything else. Click the “Start Recording” and then click the “Refresh Visuals”.
Collapse the Table and click on the “Copy Query”.
Now, go back to the Power Automate, paste the copied query from the Power BI Desktop to the Run a query against a dataset action under Query Text. You will now have something similar to this.
Then add a Create CSV table action and add the First table rows as for the From. Then add a Create File action from SharePoint. Provide the details of the SharePoint document library where you wanted the csv file to be created.
Test the flow, the flow should be able to successfully ran and a new csv file should be created in the Snapshot folders.
In conclusion, automating data snapshots from Power BI using Power Automate offers a streamlined solution for maintaining up-to-date insights. By leveraging scheduled flows and the "Run a query against a Dataset" action, users can effortlessly generate CSV files containing essential data from their Power BI reports. With careful configuration and integration, this approach enhances efficiency and accuracy, empowering organizations to make informed decisions based on timely data snapshots.
Kommentarer