Load data in Excel Online and build a dataflows monitoring report with Power BI
This tutorial demonstrates how to use an Excel file and the dataflows connector in Power Automate to create a dataflows monitoring report in Power BI.
First, you'll download the Excel file and save it in OneDrive for Business or SharePoint. Next, you'll create a Power Automate connector that loads metadata from your dataflow to the Excel file in OneDrive for Business or SharePoint. Lastly, you'll connect a Power BI file to the Excel file to visualize the metadata and start monitoring the dataflows.
You can use this dashboard to monitor your dataflows' refresh duration and failure count. With this dashboard, you can track any issues with your dataflows performance and share the data with others.
Download the .pbit file
First, download the .pbit file.
Download the Excel file and save to OneDrive
Next, download the .xlsx file and save the file to a location on OneDrive for Business or SharePoint
Create a dataflow
Create a flow in Power Automate
Navigate to Power Automate.
Select Create > Automated cloud flow.
Enter a flow name, and then search for the "When a dataflow refresh completes" connector. Select this connector from the list, and then select Create.
Customize the connector. Enter the following information on your dataflow:
- Group Type: Select Environment when connecting to Power Apps and Workspace when connecting to Power BI.
- Group: Select the Power Apps environment or the Power BI workspace your dataflow is in.
- Dataflow: Select your dataflow by name.
Select New step to add an action to your flow.
Search for the "Add a row into a table" connector from Excel Online (Business), and then select it.
Customize the connector. Enter the Location of the Excel file and the specific Table the data loads to.
- Location: Select the location of the Excel file on OneDrive for Business or SharePoint.
- Document Library: Select the library of the Excel file.
- File: Select the file path to the Excel file.
- Table: Select "Dataflow_monitoring".
Add dynamic values to the required fields.
For every required field, you need to add a dynamic value. This value is the output of the metadata of the dataflow run.
Save the flow.
Create a Power BI Report
Connect to your Excel file.
In this dashboard, for every dataflow in your specified time interval, you can monitor:
- The dataflow duration
- The dataflow count
- The dataflow failure count
The uniqueID for every dataflow is generated by a merge between the dataflow name and the dataflow start time.
Submit and view feedback for