Summary

The website content outlines a method for automating and scheduling the delivery of customized CSV reports from Power BI datasets via email using Power Automate.

Abstract

The article provides a comprehensive guide on how to leverage Power Automate to send scheduled, tailored reports from Power BI datasets to users within an organization. It begins by acknowledging the common user need for more than just screenshots of dashboards, emphasizing the ability to extract and distribute data in a CSV format. The process involves using Power BI Desktop to extract data from a semantic model, integrating this query into a Power Automate flow, and setting up the flow to run on a schedule. The flow then generates a CSV file from the Power BI dataset and emails it to the intended recipients. The guide includes detailed steps and screenshots for both Power BI and Power Automate configurations, ensuring users can replicate the process for their reporting needs.

Opinions

The author recognizes the limitation of Power BI in providing customized reports and offers a solution using Power Automate.
The solution is presented as user-friendly and easily implementable, suggesting that users with varying levels of expertise can execute it.
The use of Power Automate for scheduling and distributing reports is implied to be a more efficient and automated approach compared to manual methods.
The guide's inclusion of potential use cases, such as sending reports via Email/Teams or uploading to OneDrive/SharePoint, indicates a versatility in the application of the solution provided.
The step-by-step instructions with visual aids reflect the author's commitment to providing clear and actionable guidance.

How to Automate and Schedule CSV Report Delivery via Email from Power BI using Power Automate

Regularly, I encounter requests from users expressing a need for customised, scheduled reports from Power BI, rather than mere screenshots of specific dashboards. While this isn’t a straightforward task in Power BI, it can be done easily in Power Automate. In this blog post, we will delve into how to effectively send and schedule tailored reports from Power BI datasets to any user within your organisation via email

🎯Objective

Send a scheduled report from a Power BI Dataset/Semantic Model that was pre-built

🔨Tools Required

Power Automate
Power BI Desktop
Power BI Workspace

🪄 Potential Use Cases

Send scheduled reports to users via Email/Teams
Upload the report to OneDrive/SharePoint

✨ Solution Overview

1. Extract data from the Semantic Model using Power BI Desktop
2. Integrate this query into a Power Automate action
3. Set up and execute the Power Automate flow on a schedule
4. Distribute the resulting CSV File via Outlook Email

📝 Step By Step Guide

Power BI Steps

Open Power BI Desktop, Get Data and select Power BI Datasets/Semantic Model

Power BI Desktop-> Power BI Datasets/Semantic Model

2. Choose your desired dataset from the Semantic Model

Select the dataset from your Semantic Model

3. In the Visualizations Panel. Select Table, and drag and drop the columns to create your table

Insert a table in the Visualizations Panel. Drag and Drop Columns to build your table

4. Navigate to View-> Performance Analyzer and Start Recording

5. Refresh the visual and then stop recording

6. Copy the query (to be used later in Power Automate)

Power Automate Steps

Set a trigger for ‘Recurrence’ (e.g., once a month).

2. Add a ‘Power BI Action’ to run the query against your dataset/semantic model.

Workspace: Select PBI Workspace
Dataset: Select dataset/semantic model
Query Text: Paste the copied query text from Step 6 in Power BI Steps

3. Add a “Condition” action to check if there are no records returned from Power BI Dataset query

empty(outputs('Run_a_query_against_a_dataset')?['body/firstTableRows'])

Add a Condition Action to check if there are any records returned from the query

4. Add an action to ‘Create CSV Table’. Select the first table rows from the dynamic content

5. Add an ‘Outlook — Send an email’ action

Fill in the Subject and Body Message
Under Advanced Parameters, select Attachments
Under Attachment Name, Input Name
Under Attachment Content, select Output from Create CSV Table dynamic content

6. Test this flow manually to ensure you receive the scheduled report via email