Power Automate get data from Power BI dataset Revisited

Pulling data from a Power BI dataset has some exciting updates in Power Automate and Power BI. We now have a DAX tab to write simple DAX to pull the right data. Power Automate has had a makeover and I show you how to use Parse JSON to get more details about your data.
Blog post is coming I promise!
Table of contents
00:00 Introduction
00:38 Step 1 - Write the DAX
02:06 Step 2 - Write the flow and run to get sample data
06:04 Step 3 - Tidy up the data with Parse JSON
07:48 Create HTML Table Advanced

[@Alan-ps2ul]

Thank you - the 1st 'Compose' to generate JSON Schema has solved a problem I have spent the past 3 days working on and getting nowhere. This is such a great way to get working Schema and better than I've seen elsewhere on KZbin!

[@HatFullOfData123]

Compose rescues me soooo many times

[@pbiqueryous]

The most important video of our PowerBI times, saving lives! This is amazing!

[@michelenarvaez9271]

BLESS YOU! I have been trying for DAYS to get my large payload Parse JSON to work (the dynamic content wouldn't popular the columns). I have googled and tried multiple ways. Your video finally helped me! That Compose before the Parse JSON saved me! Thank you for posting this! 😃

[@HatFullOfData123]

Compose action saves me so many mess

[@SoumenGhoshal]

Great point. Special characters were a trouble. Also the query needs to memory efficient. Excellent video.

[@katrockhold2791]

Thank you so much! This video helped fix two of my issues. I even enlisted the support of a Power Automate expert within my company and we did not achieve the targeted result. This video helped me greatly!

[@rauljimenez5485]

Some nice gold nuggets in there, the parse and formatting of the output of the HTML table are simple yet very powerful, thank you for sharing!!

[@berggrog1]

That was really a good video. Simple and easy to follow. You do a very good job presenting stuff in an easy manner. Keep up the good work!

[@careyjohnson6630]

One of the all-time greats. 5 stars!

[@venkatr4972]

You're my Savior.. Thanks much . please post more update format in Expression

[@wadanda]

Excellent video!

[@HatFullOfData123]

Glad you liked it!

[@JannievanderWalt]

Awesome video, interestingly, I followed your first video where I copy the DAX from the Performance Analyzer where in PBI Desktop the Table has only one column and +-7000 entries. However when Power Automate Processes it, it only processes 501. Changing the DAX in the way how you did it in the DAX Query View (New feature in PBI Desktop) its shorter and simpler to read and now Power Automate processes it properly for all 7000 records. It's weird... but thank you this video also helped me understand the Parse JSON bit and I like how how you simplify the JSON by grabbing it from the Compose and not just any output... and now I know how to rename the columns at the end. 🙏🙏🙏🙏🙏🙏

[@HatFullOfData123]

Glad it helped!

[@gthornbu4109]

There is actually a simple solution to this problem. The old method using the performance analyzer sometimes adds a top n filter to the table with the limit of 501. You can find the filter in the dax query and change it to a higher number or delete it to fix it. The dax for the top n is TOPN(501, 'your table')

[@valapo80800]

keep cool!!!! keep cool!!!

[@user-lh2ip4qj5q]

you saved me, thank you

[@HatFullOfData123]

Glad it helped!

[@gabrielemannoni4668]

Bravissima!!!

[@MrTC-rv3jo]

Great video! I searched a lot for something like this! thank you! My only issue is that I need to send a scheduled email with a CSV file containing the data in a table format. I was able to do that but I got the CSV file to show me the data in the first row only and not as a table with headers and columns. Any idea on how to overcome this? thanks again!

[@MarcusCrutchfield]

This video is extremely helpful! THANK YOU! I'm just wondering if there's a reason you keep the 'Compose' data operation before the 'Parse JSON'? would the flow still work without it?

[@HatFullOfData123]

You can get rid of it as we don't refer to it. Good point!

[@sujatarao9913]

Hi, I need to write table visual data from powerbi to sql server via power automate , how can I do that ?

[@sofianeboukecha]

great video, thank you can we do the same thing to extract an XLSX file, not HTML table?

[@HatFullOfData123]

Once you have the data from the query you can do what you like including adding to an Excel table. Making a new table is a little more complex

[@sofianeboukecha]

After the Json step?

[@mirrrvelll5164]

Very useful video. Does it apply on daily refresh? I mean I tried similar to your method but I am still getting the data from yesterday, so it repeats me the "old schema" basically. How to make that dynamic?

[@HatFullOfData123]

If the semantic model is refreshed the data fetched should be refreshed. How are you specifying your date range in the DAX?

[@mirrrvelll5164]

​@@HatFullOfData123 Ah I checked the query in PA, and found that has been filtered on yesterdays date. So I built like IsToday on visual, saved it with new query and added this step in query. But I removed it afterwards (isToday) because the visual should be dynamic.. Do you think it will work tomorow wih new data`, or better question, is there an additional step in PA for like checking for todays date?

[@HatFullOfData123]

Making the DAX dynamic is the best way.

[@Bananaskin1]

Has the Dax feature been removed? I am running the Feb 2024 release and I'm only seeing the standard 3 options.

[@Bananaskin1]

Forget my previous question, I hadn't realised that there was a preview features facility

[@kumarhrithikranjan1788]

Do we need PowerBi capability licence or will the per user can do the work and do we need paid Power Automate licence for this?

[@kumarhrithikranjan1788]

Capacity*

[@HatFullOfData123]

You will need access to the dataset so you will need pro Power BI licence minimum. It's not a premium connector in Power Automate so just a free license will work.