.png)
Author: Anna Cameron
Microsoft Power Automate, part of the Power Platform, is a robust tool designed to automate tasks across various systems and applications. Its low-code interface simplifies the automation of repetitive tasks, enhancing efficiency by integrating processes between platforms.
With Power Automate, users can build custom workflows that trigger actions based on specific conditions, streamlining complex business processes. The platform supports a wide range of connectors, including Slack, Adobe, Calendly, and Google applications like Google Drive and Calendar.
In this guide, we’ll walk you through creating a daily workflow using Power Automate that queries a Power BI dataset, extracts the data, and saves it to OneDrive as a CSV file.
1. Sign in to Power Automate: Start by logging into Power Automate.
2. Create a New Flow: Go to "My flows" and choose "New flow". For this example, we'll set up a "Scheduled cloud flow" that runs automatically at a specified time. However, selecting an “Automated cloud flow” will also take you to a blank flow where you can add the “Recurrence” trigger as well.
3. Name and Schedule Your Flow: Give your flow a name and set when you want it to run. You can also set the schedule in the following step. Click "Create" to start building your flow.
4. Set the Flow's Trigger: The flow begins with a "Recurrence” trigger, which schedules when the flow's actions will occur. You can always return to this step and edit this step to adjust the run schedule, if necessary.
5. Add an Action: Click the "+" button to add a new action step. Since we want to query data from Power BI, search for "Power BI" in the “Add an action pane” and then click "See more"
6. Run a Query in Power BI: Choose the action to "Run a query against a dataset".
7. Select the Workspace and Dataset: You'll then choose the workspace and dataset where you want to run the query. First, you’ll be prompted to login in to the Microsoft Power BI tenant where the report is hosted. Once connected you will see your workspaces and datasets.
8. Write the Query: Next, you’ll need to write a query in DAX. It’s a good idea to first create this query in Power BI, so you can confirm the results before using it in the flow. Once you’re happy with the query, copy it into the “Query Text” parameter. Be sure to add an EVALUATE statement to the beginning of the query. Then, assign the DAX query to a variable and EVALUATE said variable at the end of the query.
Hint: If the data you want export is already in a table visual in a Power BI report, you can export the DAX for that specific visual using the Performance Optimizer in Power BI Desktop.
9. Format the Data: Add a "Select" action to format the data properly. Open the dynamic content list by selecting the lightning bolt icon. Set the “From” parameter to use the First table rows from the previous “Run a query against a dataset” step.
The “Select” action in this flow is used to make sure that the column headers appear correctly in the CSV file export. Specify the column names by adding them as Keys in the “Map” parameter.
Then, insert an expression by clicking the icon instead of the lightning bolt icon. Use the expression, item()?['table_name[column_name]'] , to transform the column headers pulled from the Power BI dataset. The expression will change the column header from something like “DIM_Date[Week_Start_Date]” to “Week Start Date” in the exported CSV file.
10. Create a CSV File: Add another action to "Create CSV table" and set it up to use the dynamic content Output from the previous “Select” step.
11. Save the File to OneDrive: The final step is to add an action to save the CSV file to OneDrive. You’ll be prompted to login to OneDrive to choose file path where the file will be saved. Set the file name and include a timestamp by using the expression utcNow(). A timestamp will be useful in managing and organizing the files generated by the recurring Power Automate flow.
12. Save and Test: Save your flow and run a test to confirm that the flow works. Thankfully, Power Automate provides a 28-day run history for each flow to track whether a run was successful or not.
Clicking on one of the failed runs will open the flow and show exactly what step resulted in the failure. In addition, Microsoft’s new Copilot feature will aid in troubleshooting the errors by explaining the error and providing suggestions to solve the error.
Wrap Up
Microsoft Power Automate is a powerful tool that simplifies task automation, from sending emails to managing complex, multi-step workflows.
Whether you’re extracting data from Power BI, saving it to SharePoint, or sending reports directly to clients, Power Automate helps streamline and automate these processes with ease. Its seamless integration across platforms not only boosts productivity but also helps businesses save valuable time, making it an essential asset for optimizing workflows.
Looking for clear, practical solutions to your data challenges? Let’s loop you in. Book your intro call with our data experts today.