Load Data into Excel Online and Build a Dataflows Monitoring Report with Power BI
In this step-by-step tutorial, we will show you how to set up your own monitoring dashboard for all of your dataflows:
First, you will download the Excel file and save it in OneDrive for Business or SharePoint. Next, you will create a Power Automate connector which will load metadata from your dataflow into the Excel file in OneDrive for Business or SharePoint. Lastly, you will 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.
Search for the template "When a dataflow refresh completes, output status into Excel". If you encounter difficulty, see these instructions.
Customize the flow. Actions that require input from you will automatically be expanded.
The Dataflow Refresh trigger is expanded because you need to enter 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.
The Add a row into a table action is expanded because you need to 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.
- Select the field next to Dataflow ID and then select the lightning button.
Select the Dataflow ID as the dynamic content.
Repeat this process for all required fields.
Save the flow.
Create a Power BI Report
Connect to your Excel file.
In this dashboard, you can monitor, for every dataflow in your specified time interval:
- 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.