If you follow my blog posts, you know already that I'm a huge fan of the integrations of Power BI with the Power Platform, specially Power Apps and Power Automate. I truly believe that these integrations can take your reporting solutions to the next level, and if you are not into these topics already, now is the time to learn a little bit more about it.
In this blog post, I'm going to talk about the integration of Power BI + Power Automate.
During the last few months, I really struggled to find all the information I needed whenever I was using Power BI with Power Automate. Sure the info is all there on the internet somewhere, but I couldn't find a good summary of everything you can do when it comes to the integration.
For this reason, I decided to do my own summary, so I don't ever have to check multiple different web pages to get the information I need.
Before I start explaining all the possibilities you can get from this integration, I just wanted to leave you with a brief summary of what is Power Automate, and why you should think about using it as part of your reporting solutions in Power BI.
What is Power Automate?
"Power Automate is a cloud based service that allows you to create workflows to automate business processes. With Power Automate, you are able to use out of the box connectors that allow you to connect to more than 200 services and automate repetitive tasks, saving you a considerable amount of time and effort."
I didn't tell you much did I? Well... it's a lot easier if I show you some examples of the automations you can do with Power Automate, so let me show you some templates that are available for anyone (yes, you can simply use these templates, no need for coding!!):
Hopefully you get the idea behind Power Automate now. As you can see, these are all tasks that you would probably do manually... but with Power Automate, you just need to create your Flow (e.g. using one of the templates), and then you will never have to worry about that tedious manual task again!
The templates I showed you don't exactly help you when it comes to automating tasks in Power BI... so let me show you a few of the templates available for Power BI. Again, you can just use these out of the box templates, Power Automate is really really easy to use:
To show you how easy it is to start building workflows with Power Automate, we will analyse one of the Power BI templates as an example. Let's look at the template for "Save a Power BI paginated report to OneDrive for Business":
The video above shows you the flow you get when you open the template. As you can see, you just need to make sure the important fields are filled with the correct information, and then, once this flow is saved, it will just do the magic for you automatically, how amazing is this???
So, what exactly can you do with the Power Automate integrations with Power BI?
For you to understand exactly what you can do with Power Automate, we need to go deeper than using just the templates.
To understand the logic behind the flows, there are two concepts that you need to know. Power Automate has two types of steps you can use, triggers and actions. A flow will always have one and only one trigger, but that trigger can call multiple actions.
Trigger - an event that starts the flow
Action - events/actions that you want the flow to do once the trigger takes place
There are different types of triggers and actions you can use when it comes to the integration of Power Automate with Power BI. The image below summarises these:
As you can see, there are (at the time of this post) 3 triggers and 5 actions for this integration.
These are only the triggers and actions specific for Power BI, you have loads of other different triggers and actions that you can use, as in my previous example for the template.
It's a matter of being creative, and mix the different triggers and actions available.
Now, you probably want to know more about each one of the triggers and actions. I will leave you with a few links in case you're curious, more detailed information will probably come in future posts 😊
Triggers:
> When Power BI button clicked: Power BI - Connectors | Microsoft Docs
> When a data driven alert is triggered: Power BI - Connectors | Microsoft Docs
> When a dataflow refresh completes: Announcing Dataflows Power Automate Connector public preview | Microsoft Power BI Blog | Microsoft Power BI
Actions:
> Add rows to a dataset: Power BI - Connectors | Microsoft Docs
> Export to file for paginated reports: Power BI - Connectors | Microsoft Docs
> Export to file for Power BI reports: Power BI - Connectors | Microsoft Docs
> Refresh a dataset: Power BI - Connectors | Microsoft Docs
> Refresh a dataflow: Announcing Dataflows Power Automate Connector public preview | Microsoft Power BI Blog | Microsoft Power BI
Power Automate visual in Power BI
I couldn't finish this blog post without talking about one of the most exciting announcements about the Power BI integration with Power Automate: the Power Automate visual!
This visual empowers users to embrace the automations that Power Automate can offer directly from within Power BI.
You will have a very similar experience as you have with Power Automate online, meaning you can build your own flows, but without leaving Power BI Desktop. You will then have a button you can click on, triggering the flow you've built behind the scenes.
The new Power Automate visual is still in preview, and you can get it from the AppSource:
Once you get your visual and add it to your report, you can start building your flow directly from Power BI:
As you can see from the video above, you can not only build your own flow, but you can also use existing flows.
As for creating your own flow, you can leverage an existing template and just make the changes you need, or you can build an instant flow from scratch.
After you've built your flow and saved it, you need to assign it to the visual in Power BI:
When building your flow in Power BI, you can use some information coming from Power BI service such has:
> User ID
> User name
> User email
> Timestamp
As you can imagine, just by having the chance to use this information coming from Power BI you can get very very creative with your flow!
You can also format the button in the formatting pane if you want to change it's appearance.
About licensing:
Limited Power Automate capabilities are included within Power Apps, Office 365 and Dynamics 365 licenses. If you don't have any of the licenses that will allow you to have access to some of the Power Automate capabilities, you will need to purchase standalone licenses (check the links below for more info).
Licensing guide: https://go.microsoft.com/fwlink/?linkid=2085130
Also, in most cases, to use the full capabilities of the Power Automate connectors to Power BI, your report needs to be stored in a workspace in a premium capacity.
If your report is not in a Premium capacity (I encountered the same difficulties with both Pro and Premium per user, so it really needs to be a Premium dedicated capacity), you will encounter some limitations such as:
> You can only schedule a flow to run 8x per day when using the actions Refresh a dataset and Refresh a dataflow. If you try to refresh your dataset/dataflow more than 8x a day your flow will only run 8x, after that you will get an error and the flow won't run anymore.
> Flows that have the actions Export to file for Power BI reports and Export to file for paginated reports won't run if the report is not stored in a premium capacity.
Interested in learning more about Power BI Report Design? Then the Power BI Report Design Bootcamp is for you!
