Introduction to Power Automate and Power BI

Overview

Power Automate

Power Automate is a service that allows you to automate workflows and tasks across multiple applications and services without the need for extensive coding knowledge.

Power BI

Power BI is a business analytics service by Microsoft that provides interactive visualizations and business intelligence capabilities with an interface simple enough for end users to create their own reports and dashboards.

Objective

The objective of this project is to create a Power Automate flow to automatically send screenshots of a Power BI dashboard to managers in our company.

Step-by-Step Implementation

Prerequisites

1. Microsoft Power Automate Account
2. Microsoft Power BI Account
3. Permissions to access the relevant Power BI Reports/Dashboards

Creating the Power Automate Flow

1. Log into Power Automate

   Go to Power Automate and sign in with your Microsoft account.

2. Create a New Flow

   • Click on "Create" on the left navigation pane.
   • Choose "Scheduled cloud flow" to create a flow that runs on a schedule.

3. Configure Flow Trigger

   • Give your flow a name, for example: "Send Power BI Dashboard Screenshots".
   • Set the recurrence to your desired frequency (e.g., daily, weekly).

4. Add an Action to Get Screenshots

   • Click on "New step".
   • Search for "Power BI" in the connectors list.
   • Select "Export To File for Power BI Reports".
   • Choose the Report and the format as "PDF".

5. Add an Action to Send an Email

   • Click on "New step".
   • Search for "Outlook" (or any preferred email connector).
   • Select "Send an email (V2)".
   • Configure the email:
     • To: Add the email addresses of the managers.
     • Subject: "Daily Power BI Dashboard Screenshot".
     • Body: A brief message (e.g., "Please find the attached screenshot of the Power BI dashboard.").
     • Attachment Name: Name your attachment (e.g., "Dashboard_Screenshot.pdf").
     • Attachment Content: Use the file content from the previous step.

6. Save and Test the Flow

   • Click "Save" to save your flow.
   • To test, click on "Test" in the upper right corner, use the manual test option to manually trigger the flow and ensure that the email is sent successfully with the attached screenshot.

Conclusion

By following the above steps, you have successfully created a Power Automate flow that sends a screenshot of a Power BI dashboard to managers in your company on a scheduled basis. This automation saves time and ensures that the managers are kept up to date with the latest data insights without manual intervention.

Setting Up the Workflow Environment

Step 1: Creating a New Flow in Power Automate

1. Open Power Automate: Navigate to the Power Automate portal in your web browser.

2. Create a New Flow:

   • Click on Create from the left navigation panel.
   • Choose Scheduled Flow for recurring automated tasks.

3. Define the Flow Parameters:

   • Provide a suitable Flow name.
   • Set the Start time and Repeats every parameters as per the desired schedule, e.g., "every day at 9:00 AM".

4. Click Create.

Step 2: Adding a Power BI Trigger

1. Search for the Power BI Connector:

   • In the flow design screen, click on New step.
   • Search for Power BI.

2. Choose the Power BI Action:

   • Select Run a query against a dataset.
   • Provide the necessary details:
     • Workspace
     • Dataset
     • Database connection string (if needed)
     • Query (to fetch the relevant data for the screenshot)

Step 3: Capture Screenshot of Power BI Dashboard

1. Search for a Delay Action: This allows the system to load the Power BI dashboard completely.

   • Click New step
   • Search for Delay action.
   • Set the delay parameters, e.g. Duration: 1 minute.

2. Use a Run R/Send an HTTP Request Action: Utilize the available connectors to capture the screenshot.

   • Click New step
   • Search for Power BI.
   • Select the appropriate action to capture or generate a preview of the report.

3. Save the Screenshot:

   • Provide the storage location and parameters to save the screenshot file.

Step 4: Send Email to Managers with the Screenshot

1. Use Office 365 Outlook or similar connector:

   • Click on New step.
   • Search for Office 365 Outlook.

2. Choose the Send an email action:

   • Configure the email settings:
     • To: Add manager email addresses.
     • Subject: "Daily Power BI Dashboard Screenshot".
     • Body: "Please find attached the latest Power BI dashboard screenshot."
     • Attachment Name & Content: Attach the saved screenshot file from the previous step.

Step 5: Save and Test the Flow

1. Save the Flow:

   • Click on Save at the top right of the screen.

2. Test the Flow:

   • Click on Test to ensure everything operates as expected.
   • Validate email delivery and screenshot attachment in managers' inbox.

Step 6: Monitor and Maintain the Flow

1. Monitor Flow Runs:

   • Navigate to My flows.
   • Select your newly created flow and check the run history to ensure it works automatically as scheduled.

2. Update and Debug as Necessary:

   • If any issues are identified, review the flow configuration and data connections, make necessary adjustments, then retest.

This Power Automate setup ensures your workflow is efficiently sending daily screenshots of your Power BI dashboard to your managers without manual intervention.

Implementation: Creating and Configuring the Screenshot Capture in Power Automate Flow

Step 1: Create a New Flow in Power Automate

1. Open Power Automate.
2. Select Create from the left-hand menu.
3. Choose Instant flow.

Step 2: Configure Manual Trigger

1. Name your flow Capture and Send Power BI Screenshot.
2. Add a trigger, Manually trigger a flow.

Step 3: Add Action - Power BI Screenshot Capture

1. Click on + New step.
2. Search for Power BI and select Export To File for Power BI Reports.
3. Configure the action with the following:
   • Report Export Type: PDF (Embed).
   • Workspace: Select the workspace containing the dashboard.
   • Report: Select the specific report.

Step 4: Save and Extract the File Content

1. Add another step, search for OneDrive for Business, and select Create file.
2. Configure the OneDrive for Business action:
   • Folder Path: Choose or create a folder to save the screenshot.
   • File Name: Specify a file name, e.g., PowerBIScreenshot.pdf.
   • File Content: Select the File Content from the previous Power BI action.

Step 5: Configure OneDrive File Download

1. Add another OneDrive for Business action, search for Get file content and configure:
   • File Identifier: Select the file path from the previous Create file action.

Step 6: Send the Screenshot via Email

1. Add a new step and search for Office 365 Outlook (or your email service provider).
2. Select Send an email (V2).
3. Configure the email action:
   • To: Add the email addresses of the managers.
   • Subject: Power BI Dashboard Screenshot.
   • Body: Provide appropriate text, e.g., Attached is the latest screenshot of the Power BI dashboard..
   • Attachments Name - 1: Use the same file name provided in the Create file action, e.g. PowerBIScreenshot.pdf.
   • Attachments Content - 1: Use the file content from the Get file content action.

Final Steps: Save and Test the Flow

1. Save the flow.
2. Test the flow by triggering it manually to ensure it captures the screenshot and sends the email correctly.

Flow Diagram

1. Triggers: Manual.
2. Actions:
   • Power BI (Export To File for Power BI Reports)
   • OneDrive for Business (Create file)
   • OneDrive for Business (Get file content)
   • Office 365 Outlook (Send an email)

By properly configuring each step, you can ensure that the screenshots of your Power BI dashboard are captured and sent to managers efficiently using Power Automate.

Sending Email Notifications and Distribution using Power Automate

Assuming you have already set up the screenshot capture workflow in Power Automate, below is a thorough explanation for setting up the email notifications and distribution part.

Step 1: Add Email Action to the Flow

1. Open the existing Power Automate flow:

   • Navigate to your existing workflow designed for capturing screenshots of the Power BI dashboard.

2. Add a new action:

   • Click on the + New step button to add a new action after the screenshot capture step.

3. Select the Send an email (V2) action:

   • Search for "Send an email (V2)" in the "Choose an action" search box.
   • Select Send an email (V2) from the list (provided by Office 365 Outlook).

Step 2: Configure the Email Notification

1. To field:

   • Enter the email addresses of the managers you want to notify. Separate multiple email addresses with a semicolon.

   To: manager1@company.com; manager2@company.com

2. Subject field:

   • Add an appropriate subject line for the email. Use dynamic content if needed.

   Subject: Daily Power BI Dashboard Screenshot

3. Body field:

   • Create the email body. You may include placeholders for dynamic content captured earlier in the flow. Example:

   Body:
   Hello Team,
   
   Attached is the latest screenshot of the Power BI dashboard, captured as of [Date and Time].
   
   Regards,
   [Your Name]

4. Attachment:

   • Click on Add an attachment.

     • File Name: You can provide a static file name or use dynamic content to name the file.

     File Name: Dashboard_Screenshot.png

     • File Content: Select the variable or output where the screenshot is stored.

Step 3: Save and Test

1. Save the Flow:

   • Click the Save button to save all your changes.

2. Test the Flow:

   • Run a test of your flow to ensure that the screenshots are being captured and sent correctly via email.

Example Power Automate JSON Configuration

{
    "name": "ScreenshotAndEmailFlow",
    "trigger": {
        "type": "Recurrence",
        "interval": "Day",
        "frequency": "1"
    },
    "actions": [
        {
            "type": "CaptureScreenshot",
            "settings": {
                "imageFormat": "png",
                "savePath": "/Screenshots/Dashboard.png"
            }
        },
        {
            "type": "SendEmailV2",
            "settings": {
                "to": "manager1@company.com; manager2@company.com",
                "subject": "Daily Power BI Dashboard Screenshot",
                "body": "Hello Team,

Attached is the latest screenshot of the Power BI dashboard, captured as of [Date and Time].

Regards,
[Your Name]",
                "isHtml": true,
                "attachments": [
                    {
                        "name": "Dashboard_Screenshot.png",
                        "content": {
                            "source": "/Screenshots/Dashboard.png"
                        }
                    }
                ]
            }
        }
    ]
}

Final Verification

1. Verify Email Delivery:
   • Ensure the email is received by the target managers with the attached screenshot.
2. Check Screenshot Accuracy:
   • Verify the screenshot's accuracy to ensure it reflects the correct state of the Power BI dashboard.

By following these steps, you should have a functional automated email notification and distribution system based on your established workflow in Power Automate.

Testing, Troubleshooting, and Optimization

Testing the Power Automate Flow

1. Manually Trigger the Flow:

   • Navigate to your Power Automate portal.
   • Select the flow you have created from the list.
   • Click on the Run button to manually trigger the flow.

2. Validate Screenshot Capture:

   • Check the location where the screenshot is saved (e.g., OneDrive or SharePoint).
   • Ensure the screenshot displays the intended Power BI dashboard correctly.

3. Verify Email Notifications:

   • Confirm receipt of the email in the manager's inbox.
   • Check the email content for the attached screenshot as per your configuration.

Troubleshooting

1. Failed Email Delivery:

   • Check Connection Errors:
     • Ensure your email provider's connection in Power Automate is active.
     • Verify credentials and authorized permissions.
   • Inspect Run History:
     • Open the specific flow run.
     • Identify any red-flagged actions and review error messages.

2. Incomplete or No Screenshot Captured:

   • Check Power BI Access and Permissions:
     • Make sure the correct permissions are given to the Power Automate flow for accessing the Power BI report.
   • Screenshot Method Validation:
     • Review the screenshot configuration steps for any missed settings or incorrect parameters.

3. Flow Failure:

   • Examine Logs:
     • Check Power Automate’s run history and logs for any failed actions.
   • Update Actions:
     • Ensure all service connections (Power BI, Email Service) are updated and active.
   • Re-run After Fixing Issues:
     • After addressing the issues, re-run the flow to verify if the errors are resolved.

Optimization

1. Efficiency in Screenshot Capture:

   • Scheduled Runs:
     • Set intervals for capturing screenshots during non-peak hours to avoid server load.
   • Performance Tuning:
     • Optimize Power BI dashboard loading time by improving DAX queries and reducing data size where possible.

2. Email Notification Load Management:

   • Batch Processing:
     • If sending to multiple managers, consider batching email notifications to reduce server load.
   • Email Content Optimization:
     • Minimize the email body content size to improve sending speed.

3. Flow Performance Monitoring:

   • Integrate Analytics:
     • Use Power Automate analytics to monitor flow performance and detect bottlenecks.
   • Custom Logging:
     • Add custom logging actions to capture additional details at key steps for fine-grained monitoring.

Example of Custom Logging Action:
1. Add an “HTTP” action to send logs to a logging endpoint.
2. Log the start and end of significant steps like screenshot capture and email sending.

{
  "method": "POST",
  "url": "https://your-logging-endpoint.com/logs",
  "headers": {
    "Content-Type": "application/json"
  },
  "body": {
    "step": "screenshot_capture",
    "status": "started",
    "timestamp": "@utcNow()"
  }
}

Apply these steps to ensure a reliable, efficient, and optimized flow within Power Automate for sending Power BI dashboard screenshots to your managers.