Power Automate – Get data from a Power BI dataset

Last modified date

Power BI datasets contain a rich useful data resource that would be really useful in a Power Automate flow. In this post we will walk through how to get data from a Power BI dataset and then email that data. This is just one example of how you could use data fetched from a Power BI dataset behind a report.

YouTube Version

Thumbnail from YouTube

Starting the Flow

In this example we want to send a monthly quick summary. So I started by creating a scheduled email, repeated every month. After I enter the flow name and change the repeat to every month rather than the default every minute! Now I can press Create, which after a few moments creates a flow with the recurrence step.

Screen grabs showing the Scheduled cloud flow tile and then the first dialog to enter in the flow name and then select repeat monthly. Finally a screen grab of the flow with the recurrence trigger.

Fetch Data using a Power BI step

Click on New step to start the process. In the search box type in Power BI and click on the Power BI tile when it appears. Now you can search the Power BI actions. In the search box enter in query and this will filter to 2 actions. The action we want is Run a query against a dataset.1

Screen grabs showing the search for Power BI and then searching for query in the Power BI actions.

When the step appears, I select the Workspace and Dataset I am interested in. The query text is expecting a DAX statement. Don’t worry you don’t have to write the DAX by hand, we can get Power BI desktop to do it for us.

screen grab of the Run a query step with the workspace and dataset populated, but the query text is blank.

Writing the Query Text

Power BI desktop can write the query text for you. You need to open the Power BI report or a report connected to the dataset. On a blank page add a table visual. Populate the table with the data you want to use in Power Automate remember to apply the correct filters. I also recommend you remove totals unless you want the totals row in your data.

In my example the table shows Total Sales and Sales Growth for products in the previous month, using Offset Month column from my calendar.

Screen grab showing the build a visual pane, filters on this visual and the Totals setting turned off to build the table, also shown

After creating the table, on the Optimize ribbon tab click on Performance analyzer2. When the pane appears on the right hand side, click on Start Recording. Then you click on Refresh Visuals. This tool is usually used for analysing how long each visual takes to refresh and part of that is to provide the query being run.

Toolbar showing the Perfomance Analyser on the ribbon and then the pane that appears

Expand the Table line by clicking on the +. Then click on Copy Query. Now you can return to the flow in Power Automate and paste the query into the query box.

Power Automate step with the query pasted to get data from a Power BI dataset

Sending the Email

The final part of this process is to create the email to send the details. The Power BI step returns an array of the rows of data. The Send an Email action would like this array converted into a html table. So we need 2 actions, create HTML table and Send an email. I am not going to make the table or email pretty. That is a separate post!

2 steps in Power Automate to create html table and then send the email.

Testing the flow

I would test as I added each action to the flow, even on a short flow. Obviously once completed I’d test again.

Screen grab showing the 4 steps in the flow all with green ticks and the email as the final result.

Resources

The report used for this example can be found on my GitHub

Conclusion

Remember its Power Automate not Power Integrate. So yes you can load a long list into Power Automate but this is not a good tool to load large quantities of data into a new system. This is a brilliant feature though that unlocks data in a dataset for an automation process.

  1. The other action I have no idea how it works and have yet to find any documentation []
  2. In earlier versions of Power BI it was on the view ribbon []

Over 20 year experience at being passionate about training, solving problems and loving a new challenge especially in the Microsoft's Power Platform suite.

7 Responses

  1. Worth calling out that this approach will translate outputs of you above the built in limits – so it will run successfully but not return all of the rows and/or all the columns specified in the DAX.
    One approach to overcome this is to identify which columns you can partition your data on, and then have an outer loop that steps over the unique value combinations of your partition columns and the generates outputs for the partition. It is tedious, but if you’re extracting large volumes of data out of powerbi this is your reality

    • A really good point. I’ll add it into my post, hopefully though not to many people are extracting large volumes of data using Power Automate, there has to be a better alternative surely.

  2. Hello,

    Thank you for providing this information 🙏. I have a few requirements and would like to know if they are possible.

    I want to create a schedule for a data flow that runs daily. I have Power BI data and I only need specific columns like name, phone number, and city. However, I want to save/ store this data in JSON format (an array of objects format:-
    [{Name:”xyZ”, phone:728789, City:”xyZ”}]) in Azure Blob Storage with the filename in the format dd/mm/yyyy hh:mm (for example, test 26-July-2023 03:50:33).

    Could you please guide me on how to achieve this kind of automation? I’m looking forward to your response. Thank you!

    • Hi
      Daily schedule can be done with a scheduled trigger. When you build the table select the right columns before you do the copy query trick.
      The next part you need to do is a Parse Json action to make the next steps easy. Then use a Select action if you need to rename the columns. Power Automate keeps arrays in a JSON format so if you use a compose action that will convert it to a string. I’ve not used Azure Blob Services actions so I can’t help you there, but I could create a file in SharePoint or OneDrive using the output of the compose as the file content. Image of my flow is here https://hatfullofdata.blog/wp-content/uploads/2023/08/2023-08-07_21-29-30.jpg

  3. Hi,
    Great tutorial. Do you know how RLS can be applied to the email recipients, so each person only sees their own data?
    Thanks!

    • That is something to explore! Work through a list of people and send them emails with rls applied… I think so, will have to play