Power Automate get data from Power BI dataset Revisited

hi I'm Laura GB and in this video we're going to revisit getting data from a pbii data set using par automate there have been lots of exciting updates since the last time I did this video and there have also been some requests for some extras so let's get started we're going to it's three steps so the first step is to write some dacks so here we are in our pabar report um I published but to write some decks we need to look at this report in PBI desktop so let's switch across to desktop and in November 2023 the update included a way to be a right Dax and test it out inside your report so up here on the left there is a little Dax button so we're going to go into there and you can write some dacks in here and you can run it and when you run it you get to see it down the bottom so I've got some pre-prepared dacks cuz you don't need to see you don't you don't need to sit and watch me type DXs but I will explain it um so in here we've got a summarized columns which basically gives you a little table and so it's a bit like a pivot table you remember in in Excel so we've got the products the category so that's going to give me um fruit green veg things like that and then I've got two measures total volume and total sales and I've given those columns a heading um the calculate table filters it down to week off set minus one I.E last week so if I run that code there we are last week we sold 151 boxes of fruit um £ 557 so that's giv me the data and so therefore I've got a query okay it normally will start with evaluate which is just basically run this code so I'm gonna take that and I'm G to copy it contrl C and then so that's given us that's step one so step two is to write a flow to use that Dax so let's switch back over to my browser and let's go to par toate and in here I'm going to go to create and we're going to go to a scheduled Cloud flow so this is something we're going to run every morning sorry not every morning every Monday morning the Monday report okay and so this is going to be every week and you get to choose which days it's going to run in that week so therefore on a mon on Monday every week create and here's the next update okay the next update is par toate has changed the the what it looks like when you're editing it and there's some nice changes in here so I've got the recurrent step if I click on the plus and then I click on ADD action over on the left hand side here it gets you add an action so the first thing we're going to do is we're going to go and find that powerbi query step and as soon as you search for it up comes my list and there we are run a query against the data set so I'm going to click onto there right so the next thing we do is we're going to choose a workspace so I've got a sales workspace my data set is sales report be aware if you just paste the Dax in okay you'll it it'll bring in special characters and that makes it not work so what you need to do is you need to convert it into text so paste as text so you can go VI pasting into um notepad or something like that that quite often fixes it but the way I use is Windows history okay okay so up comes my my things I've copied to my clipboard I click on the three dots and there is a paste as text button and I'm going to put Links of how to do that in the blog post link down below so there we are there's my query text so that's my three parts great done that's going to work so I've run this a few times so the next part okay is I want to work out what that data looks like when it comes out so what I'm going to do is I am going to add another action over here and on the left again I am going to search for a compose and there we go there's my data data operation compose and the inputs that I'm going to put into there I'm going to use the dynamic and there we are we get to see what our powerbi action will return and it's not a lot Okay so I can't I I can't go find individual Fields all sorts of things so first table rows is actually what we want so I'm going to go with that I am going to save and we're going to run that okay so I'm going to press the test button and go through all the buttons you have to press to get it to run and there we go get these lovely get these lovely green ticks it tells you how long it took to do that stage and then I've got the compose selected CU you see there's a blue line around it and if I look over on the left hand side you can see the inputs and the outputs that go into a step and here is my outputs and it's showing you that you get products and square brackets category and then square brackets round volume and all those parts so that's fine but I want to tidy these things up I also want to format that sales number so that's step three is to tidy up that data so I am going to copy that output cuz I need it to do a past Jason so copy that and let's go back into editing it so we are going to drag this over here a second which is quite nice you can drag this around so I'm going to do a plus add an action and we're going to go for a PA Json so it's another data operation and there's PA Json and it asks you what's the content well the content I'm going to use for the dynamic there is the first table rows again the schema I'm not here to write Jason schemas by hand so there's a nice link down here called do sample payload and that's why I copied it okay so what we do is we paste into here the output from the run and then I click done and it writes for me the schema so there we are it tells me that my product category is going to be a string volume is an integer etc etc it works it out for you which which is brilliant makes it easy so the next part okay is I now want to create a little HTML table and then I'm going to post that to teams okay so we're going to go into just like I did in my previous video but this time we're going to add a Twist so I'm going to add an action and up here I'm going to put in HTML Table and there we go another data operation create HTML table and where I the array that's going to come from is the pars Json body okay and advanc so this is this is the slight Oddity in this new format Advanced parameters showing zero of one no I just want you to show all of them and there we are columns automatic I'm going to change that to say custom and then gives me the option to enter my own columns so I can put into here category enter the value go to the dynamic there and there you are see body products category put that into there then let's go for well how many boxes of that category did we sell so I'm going to click into there and this is going to be a straight just a volume then we're going to put into here the value the sales value and this time I'm not going to go on the the lightning bolt I'm going to go for the FX so I can write a function and up comes a little box that I can enter that into okay and there is a format yeah let's ignore the part that it's seethrough a format number Open brackets and I'm going to go format the body sales and then we're going to put in a format string which is going to be let me escape to get rid of that gray box you put it in single quotes this is the bit that gets me every time you put it in single quotes we're going to put in a a pound sign and then I'm going to do the standard one of comma separators and two decimal places that will work fine that works for me and then we click add down the bottom here and there we go I've got my my my HTML table and what we're going to do with that is we are going to add another action add an action and we are going to send a message in teams that's interesting it doesn't actually I don't mean send I mean post my apologies post message in chat or Channel we can post as the flow bot that's not a problem and we are going to post in a channel and we're going to pick the team I've only got one team contoso and the channel That We're going to post in into there is my sales results and and what we're going to put in there is a heading of last week sales and I'm going to put into the lightning bolb to get the dynamic content and the output of my create HTML table okay so that's that part done let's save and let's do a test click all the buttons do the thing there we go a whole bunch of green ticks and let's go and have a look in teams and there we go we've got a table put in there with our things in there we we we managed to choose the headings we formatted text so that was taking data from a p data set tidying it up a bit and posting into teams using power automate I'm Laura TP if you haven't already please press subscribe take care now

Channel: Hat Full Of Data

Views: 4,373

Keywords:

Id: ByIt2h5m4S0

Length: 12min 5sec (725 seconds)

Published: Thu Nov 30 2023