In this tutorial, Jonathon Silva demonstrates how to integrate Power Automate, SharePoint, and Power BI to streamline data updates. The goal is to automate the process of taking email attachments and using them as a data source for Power BI reports, eliminating manual repetitive tasks.
Why Automate This Process?
- Manual updates are time-consuming and prone to errors.
- Automating ensures the latest data is always available.
- Scheduled refreshes keep reports up-to-date without intervention.
Use Case Overview
Jonathon starts with a Power BI report built from dummy data about superhero purchases. Each week, new data arrives via email attachments. Instead of manually uploading these files, the process will:
- Capture email attachments automatically.
- Store them in a SharePoint document library.
- Refresh the Power BI report to include the new data.
Step-by-Step Process
- Prepare Power BI Report:
- Connect Power BI to a SharePoint document library.
- Combine multiple files into one table using Power Query.
- Create Power Automate Flow:
- Navigate to Power Automate and select Create → Automated Cloud Flow.
- Set the trigger: When a new email arrives in Office 365 Outlook.
- Apply filters:
- Include attachments only.
- Filter by subject (e.g., “Superhero Order”).
- Get Email Attachments:
- Use the Get Attachment action from Outlook.
- Map the message ID and attachment ID from dynamic content.
- Handle multiple attachments with an Apply to Each loop.
- Create File in SharePoint:
- Use the Create File action.
- Specify the document library and folder path.
- Set file name and content from the email attachment.
- Test the Flow:
- Send an email with the subject filter and attachment.
- Verify the file appears in SharePoint.
Final Verification
After saving the flow, Jonathon refreshes the Power BI report. The new data appears instantly, confirming the automation works. This integration ensures that reports remain current without manual intervention.
Benefits of This Integration
- Eliminates repetitive manual tasks.
- Ensures accurate and timely data updates.
- Supports scheduled refreshes for continuous automation.
By leveraging Power Automate and SharePoint, businesses can maintain dynamic Power BI dashboards effortlessly. This approach enhances productivity and data reliability across teams.
Stay tuned for more tutorials on integrating Power Platform tools to optimize workflows. Subscribe to Pragmatic Works for the latest updates!
Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power Automate and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.