Project Details

The objective of this project was to automate the process of regularly emailing Power BI reports to external clients, a task complicated by Microsoft’s built-in limitations. Although Power BI provides a subscription feature to send reports via email, this feature is restricted to users within your organization and requires a Premium Per User (PPU) license. Additionally, sending these reports to external clients would necessitate upgrading to a P1 license, which can be cost-prohibitive.

Given these constraints, I sought to create an efficient and scalable solution for emailing reports to external clients without incurring significant licensing costs.

Solution

To address this challenge, I developed a workaround that leverages SharePoint and Power Automate to enable the automated delivery of Power BI reports via email to external clients. By utilizing SharePoint as a central hub and Power Automate for workflow automation, this approach bypasses the need for a costly P1 upgrade, while still providing a seamless user experience for report delivery.

Here’s the step-by-step process:

1. Request Report via Subscription: A subscription is set up in Power BI to send an email copy of the report to an internal user. This email contains the report in PDF format, which is sent to a designated inbox.

2. Store Reports in SharePoint: Power Automate is then used to monitor the inbox and automatically save any report PDF attachments to a SharePoint document library. This acts as a secure and organized repository for all the reports.

3. Manage Report Distribution via SharePoint List: A SharePoint list is utilized to manage report distribution. This list allows the user to specify which reports should be sent to which clients. Each entry in the list corresponds to a client and their preferences for report delivery.

4. Automate Email Delivery: Power Automate is configured to send emails based on the selections made in the SharePoint list. For each client, the appropriate report is attached to a custom-composed email, ensuring that the right data reaches the right recipient on time. The system automates this process entirely, eliminating manual intervention.

Benefits

This solution not only bypasses the restrictive licensing issue but also provides additional flexibility in managing report distribution. By using SharePoint as a centralized repository and Power Automate for task orchestration, this approach enables organizations to:

Cost Efficiency: Avoid the high costs of upgrading to a P1 license while still delivering reports externally.

Scalability: Easily scale the solution to accommodate more clients or reports by adding entries to the SharePoint list.

Automation & Flexibility: Ensure timely and accurate report delivery with minimal human intervention, and allow users to customize report sending rules via the SharePoint list interface.