Power Automate get data from Power BI dataset

Power BI datasets contain a brilliant source of data that would be useful in a Power Automate flow. In this video we do a simple example of pulling sales results from a Power BI dataset and emailing it to a manager.
Blog post to support video
hatfullofdata.blog/power-auto...
Report and data used in this video can be found here
github.com/Laura-GB/DemoData#...
Table of Contents
00:00 Introduction
00:12 Look at Report
00:33 Create Flow
01:49 Create Query text in PBI Desktop
03:35 Use Performance Analyser
04:20 Put Query text into Power Automate
04:41 Add Create HTML Table step
05:04 Add Send Email step
05:50 Test the Flow

[@urbatin9355]

I struggled for a week trying to get 1 flow to work and less than 5 minutes into a video you made this so easy. THANK YOU!

[@HatFullOfData123]

Thank you. It's what I try to do

[@user-si7ly9ry1r]

I came on line to find this EXACT requirement, and find it from the woman that taught me power bi. I'd call that a successful day Mrs GB

[@johntwohig6106]

Thank You! That opens up so many possibilities that wouldn't be possible trying to connect to the data in Power Automate.

[@HatFullOfData123]

Glad to help!

[@Bananakillaaa]

I have been struggly hard to get the DAX query. Your video has been life-saving! Thx

[@HatFullOfData123]

Great to hear!

[@AnhNgoc-tr6yp]

Thank you very much. This video is really simple and easy to understand

[@saharaharris]

Fantastic tutorial - thanks!

[@vkings6760]

Excellent tutorial, the query part is really hidden so thanks for clearing it up

[@TejinderSingh-es9wh]

Thanks, This is the only way I found to solve my problem of PowerBi limiting only one data alert per hour.

[@HatFullOfData123]

Yes alert is very limited in what it can do.

[@berggrog1]

Nice work. Simple video that shows a simple solution nicely explained. Thank you!

[@HatFullOfData123]

Last week I did an updated version with a few more extras

[@BadHoneyBadger]

You are my new best friend. First excellent explanation of this process I have found. Thank you so much.

[@HatFullOfData123]

Glad you liked it. I revisited this topic to rename the columns and make it pretty kzbin.info/www/bejne/eKqspWWeatJniZI

[@KatieHoke]

so helpful! thank you!

[@danny1206ec]

Thanks for your sharing. I am struggling on the DAX query and the video is really helpful for me.

[@HatFullOfData123]

Glad it helped

[@tomr9969]

Nice! Thanks for sharing!

[@HatFullOfData123]

Thanks for watching!

[@capitanmandril]

Thank you very much

[@l.andreamolinalozano2196]

thank you! your video is amazing 😃

[@zerobars3]

Great Tutorial, well explained. Thanks

[@HatFullOfData123]

Glad it was helpful!

[@Milhouse77BS]

thanks, and you can test RLS for the query also. Nice demo.

[@user-sp3ge5fq8q]

thanks! it was helpfull

[@user-kk2qm5vf4i]

Thank you for this video, it's life saving

[@HatFullOfData123]

You're so welcome!

[@nataschalaros418]

Thanks! Is it also possible to further specify the email list based on the dataset in the report? Eg. dataset Data Quality + related buyers: send only data of each specific buyer to related buyer email?

[@careyjohnson6630]

You da best

[@HatFullOfData123]

Thank you!

[@yadukrishnankv5265]

Thank you for the video @Hat Full of Data! Would I be able to query each column separately using this method and paste into a SharePoint list? Thank you!

[@CigarDoug]

Simple answer: Create a different visual with just one column, and copy that query to your Flow. Repeat for each column. Better answer: Create a query from a table with all of your columns, and manually edit that query to only return the column you want. If you try the simple and understand what the query is saying, you can figure out the better.

[@SS-lv8pn]

I was wondering if below scenario is possible with power Automate or not ?? Let's say if we want cross report slicer sync in power bi , this feature is currently not available in PBI , is it achievable using Power Automate

[@razibhasan1845]

Hi Laura! Firstly, thank you so much for this awesome video. It explained the whole concept in a very lucid manner. Really appreciate it. Now, I have a question. Say, I want to automate the whole process for different filters (-30 to 30) and save those tables according to the applied filter names. How should I approach? Thanks in advance.

[@HatFullOfData123]

I would have a list of the different parameters maybe in an Excel list. Then use a for each to loop through them using a concatenate to create the query to send the queries. And then save the output as csv files. Hope that makes sense

[@SoumenGhoshal]

These steps are not working. Running a query using power bi dataset results in an empty table and further steps don't execute.

[@jhonmedinahuanacomedina7208]

Hola, no viene al tema del video pero, como puedo cambiar el interfaza de panel de informe, veo el area de trabajo es amplia y los iconos son pequeños. Gracias

[@ritamgiri5872]

So, I came across a requirement to establish that I have to do the same thing except sending the data into a mail, I have to to add a new row to a dataverse table. Do you think is it doable?

[@kdatany1]

Amazing, thank you! Is there a way to update or rename the header name of the generated table?

[@HatFullOfData123]

Yes there is. I'm prepping a revised video showing the rename

[@anandreddy2992]

hi thanks for the video, but this method has a limitation of rows, how to import all the rows into a csv table & then place it in one drive/sharepoint, I have already tried the mentioned flow but not getting all the rows in my csv.

[@HatFullOfData123]

Yes it has a limitation on the row count. It's not a tool for doing large extracts

[@jean-christophehoule3669]

Hello, great video. Instead of sending an email, I'd like to move the content gathered from the query to a sharepoint list. Do you think its something fesable? If so, any documentation that you can share that would help me?

[@felixsaint-gelais-nault3028]

Do a compose on your data, see how it's formatted and use it from there

[@KellseaGeers]

Can you help advise how you would clean up the row headers from the HTML table? Thank you!

[@HatFullOfData123]

I'd recommend adding a parse json step after the get data and then look at the advanced step of create table and it gives options to rename the columns.

[@anuragsingh711]

can you please refer me to some documents please

[@adamphaneuf4618]

Hi ! It seems like I don't have power BI in the section "choose an operation". Do you have any idea how I could had it ? Thanks

[@HatFullOfData123]

I have no idea. Power BI can't be blocked and doesn't need a premium license. You on a GCC tenancy?

[@bc67]

Hello. I have hyperlinked text in a Power BI table visualization that I’d like to bring in to Power Automate and then include in an email while preserving the clickable links. Do you know if that’s possible?

[@HatFullOfData123]

Yes I think so. You'll need to build a html tag hyperlink. So you'll need the url as part of the data.

[@alexwalker7023]

Hi Laura, is there any way of doing exactly the same thing but to an excel table instead of email? I have some data in excel that needs to have live feed of data that comes from power bi table just like the one in your video, the powerbi data comes from a direct query so I can't edit it in power query unfortunately 😕 thanks, Alex.

[@HatFullOfData123]

Would a CSV file be okay? that's easy

[@niravparmar6545]

@@HatFullOfData123 Hey, Thanks for the video. How can I add .CSV file to the email instead of an HTML table.

[@tyopapsgaming3471]

how to make it dynamic? tried this flow however, whenever I refresh the Powerbi data, it only captures sa data prior to refresh

[@HatFullOfData123]

Are you refreshing the dataset in the same flow? If you are the refresh a dataset action just starts the refresh and doesn't wait for it to complete.

[@poonamk8335]

Instead of sending email step, how to take this data and copy to a dataverse table? any video is available for that? please help. thank you

[@HatFullOfData123]

One method would be to use an apply to each step to loop through all the rows and use an add item to dataverse. If it is huge number of records there might be better methods

[@user-sy8qs9xx3e]

What permissions do we require on the powerbi dataset to be able to query from power automate?

[@HatFullOfData123]

Access to the dataset, so visitor won't work, contributor upwards will.

[@user-sy8qs9xx3e]

@@HatFullOfData123 is there any way to be able to query the cube data with only read access?

[@HatFullOfData123]

@@user-sy8qs9xx3e I don't think so but I will have a play

[@johns8903]

A bit under whelmed -what good it is, sending a few html records. Pls talk about sending 10s of thousands of records as an Excel file or csv or pdf attachment. Thanks

[@HatFullOfData123]

Sending a quick update to a manager as a teams message is a regular request. If you are handling 10s thousands of records you could save as csv as an action but Power Automate is not the best tool to handle large record counts. It is not a Data Integration tool. Azure Data Factory, Fabric or other options would be better suited in my opinion.

[@TheKwattK]

Can you make a video or comment how to format the table? I am stuck on this. My table has several columns and shows a lot of brackets and Column names that will not be familiar to the user.

[@HatFullOfData123]

Already done. Take a look at this video and see if it helps kzbin.info/www/bejne/eKqspWWeatJniZIsi=7Dqs3qc01DliCd4I

[@TheKwattK]

@@HatFullOfData123 Got it thank you so much!!!

[@ivanarango4973]

Im trying to append the extracted data into an excel but it always ends up adding 1 empty row. Anyone can help me?

[@HatFullOfData123]

You looping through adding rows? Is the blank row on the top or bottom?

[@harshchovatiya-gh7hn]

my data output is coming in one row instead of table ... can you please guide?

[@HatFullOfData123]

Have you just selected measures? What's the DAX?

[@harshchovatiya-gh7hn]

@@HatFullOfData123 its work now. Accidently I Select wrong DB or something. But its ok now, Thanks.

[@sheshikumarausheni9428]

That's a very useful video Can me make same to send in watsup group user

[@davidarbuckle9636]

Excellent - But - what an overly complicated application Power BI is. Bring back Microsoft access, that was so much more User friendly!!

[@HatFullOfData123]

I think you are forgetting the oddities of Access which I loved working with. Both have their strengths, quirks and delights.