Summary

The web content provides a detailed guide on automating and scheduling the delivery of summarized tables from Power BI datasets to a Teams channel using Power Automate.

Abstract

The article outlines a method for automating the generation and distribution of customized reports from Power BI datasets. It addresses the need for more than just static screenshots of dashboards by leveraging Power Automate to send scheduled, summarized reports to users within an organization via Teams channel posts. The process involves extracting data from a Power BI semantic model using Power BI Desktop, integrating the query into Power Automate, setting up a scheduled flow, and posting the resulting summarized table to a Teams channel. The guide includes step-by-step instructions for both Power BI and Power Automate, ensuring that users can create and schedule their own tailored reports without manual intervention.

Opinions

The author identifies a common user request for customized, scheduled reports from Power BI, indicating a gap in the platform's native capabilities.
The use of Power Automate is emphasized as an effective solution for automating tasks that are not directly supported by Power BI.
The article suggests that the described automation process can enhance organizational efficiency by eliminating the need for manual report generation and distribution.
By providing a solution that integrates Power BI with Teams, the author implies that cross-platform integration can lead to more streamlined workflows within an organization.
The inclusion of potential use cases, such as sending reports via email or uploading to OneDrive/SharePoint, suggests that the solution has versatile applications beyond just posting to Teams channels.

How to Automate and Schedule Summarized Table from Power BI using Power Automate to Teams Channel

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 Teams Channel Posts.

🎯Objective

Send a summarized table 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 Summarized Table via Teams Channel

📝 Step By Step Guide

Power BI Steps

Open Power BI Desktop, to to 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 HTML Table’. Select the first table rows from the dynamic content. This action will create an HTML table that we will use to send to a Teams Channel

5. Add an action to ‘Post message in a chat or channel’.

Post As: Select Flow bot/User (yourself)
Post In: You can select to post it to a Teams Channel/Chat with Flow Bot/Channel.
Channel: In our case, we chose the Teams Channel, so we will select the Teams Channel that we would like Flow Bot to post to
Message: Select Output from ‘Create HTML table’ dynamic content

Post HTML Table in a Teams Chat or Channel

6. Test this flow manually to ensure you receive the table in your designated Teams channel

Summarized Table automatically posts in Teams Channel based on the scheduled time