Using Power Apps, Power Automate, and Power BI Together!

[Applause] [Applause] proof that i really am the person in the picture there you go yeah so i'm uh steve campbell i am uh senior data and ai consultant at concurrency um you know milwaukee based ralston brookfield based um consultancy i'm also run a power bi course at uh university wisconsin milwaukee and we actually have our first class next month which is all going to be online done a lot of power bi teaching i'm definitely a power bi guy that's my my tool i've been using power bi pretty much since it came out five years ago been in a lot that's the area of my focus um a lot of this before i've done stuff in azure you know doing data factory moving data modeling i'd say power bi site and definitely more more the data data modeling that kind of stuff and then a little fun fact here yeah obviously from the uk so i lived there i lived for a year in australia and i've also lived for the past eight years here in milwaukee so being around uh some different areas it's been cool traveled a lot and then definitely please add me on social media uh linkedin is is what i use the most um here linkedin twitter you know power bi steve you can tell i'm a power bi guy and then i also blog on power bi tips um for those i'm familiar it's a it's a really great website for power bi tips as you said a lot of the tools out there as well you've just released business ops beta which is a tool to um extend power bi for external tools and i've got a cool external tool out there so you can see all of that if you head over to power bi tips um you'll be able to find me on there if you can't remember the whole url awesome thanks steve yeah all right change gears there we go my name is anthony escobedo i'm a technical architect i've been doing this for just about five years and i've been doing it for about eight so been uh you know having a lot of fun and continue to have fun in in the different realms here so power platform teams sharepoint one drive you know things of that nature you know everything around sharepoint everything around power platform is it's kind of where i've spent a lot of my time i say travel is key because you know just getting to know different people you know i i think uh steve just being a part of our our group here it's awesome to have you know his accent and his you know personality in our group and being able to add that in and not only that it's you know just being able to get out and and see how other people work so you know when it comes to like the power addicts around the world where you're working with people through you know twitter or linkedin and you're having those conversations their personalities really come out and uh it's it's actually uh it's a great thing to to be able to meet some of these people face to face so uh you know i've been to cuba and costa rica uh bali and other areas of europe and you know one place i haven't been to is the uk so steve maybe i should just get a cheap flight out there and you know take up half the plane for myself like uh the flights are now so maybe that would be worth a little hard to it right now but soon yeah yeah you'll make room for me right of course uh perfect and then you can see here yeah please add us on linkedin um and twitter those are our two spots there uh you know i'm i'm huge into both of them and i try to post as much as possible especially when there's you know tools deprecating or new new tools coming out um how i use tools and how it's successful for clients so i'm just trying to you know bring that about so that it can be as available as possible for other people too so as i learned things i like to share that and i would uh definitely encourage you guys to do the same uh feel free to uh and actually i got i got a message just today or yesterday asking about you know are there any tips and tricks you know on this certain subject and uh you know i i said sure i'll get back to you early next week and you know we're gonna you know set up some time to you know talk through that and it's just as simple as that is just reaching out to some of these people whether they're local or farther away but i mean the worst thing you can get is no so certainly asking and just kind of putting yourself out there on twitter you know i'll do a little bit of personal things on there too i have i have two kids um and uh my youngest just turned one so it's it's really exciting and kind of going through that with them but you'll see some family things that pop up on twitter too so you'll see a little bit more of a personal side of me on there but um the majority of things are are professional and around power platform and just kind of the fun smaller less bloggy type of posts are on twitter so something you can find there and then on my personal website uh it's just a spot for me to you know expand on some of these you know longer topics if i solve the problem that i didn't find any you know anywhere else you know i just i'll put it out there um one of them being you know an on-prem gateways and being able to walk somebody through you know how to connect to that you know what it means to connect and you know how to reuse connections and it was like this big thing that spent a little bit of time doing and i wanted to make sure that we could share that along with you know other people and there's a couple other ones that are out there and i'll continue to post there as uh you know there's more topics so i'm certainly gonna i can see myself posting a lot more in the upcoming months especially with this user group just kind of seeing more of the things that people are running into and being able to help solve those problems so as we dive into the demo today we're going to show a little bit about power bi a little bit about power apps and then you know just a simple notification and power automate and how all of these uh you know work together great so you know the topic of today is power bi power apps power automates kind of better together we really want to show this uniform product here and how we can use everything together so let me put up this power bi report i have this is a report i've made um one thing what's really great about power bi you know really good at viewing your information looking at historical information natively there's no real write back for power bi you can't insert data there's nowhere to really say hey i want to change this i want to add notes something like that so on its own it can't do this but using the tools together we can actually make one product where we use a power bi a power app and a flow you know power automate to really have some data so looking at this this is just some some fake data you'll probably see this data set a lot we've got um my fake company here which has different um stuff we sell here we can have some consulting we can see insider consulting we have different products that we consult on very varied and do a lot of different consulting for different things now down the bottom we have our forecast you can see that this dark blue is actually our forecast and the green is the historical sales now this forecast is you know power behind we've got our data scientists here we do some machine learning to kind of try and predict our sales however one thing with forecasts is that we actually sometimes want some input from the sales people our ai is good but sometimes i want to let our sales people make adjustments maybe they know something that hasn't been picked up in our models for example they know maybe one of the stores is just goes down or a customer's going to have a business something that's going to really affect you know some sales and isn't picked up by the model so we really wanted to include a way on how we can write this back into the data and have our sales people who are looking at the data be able to to add notes and adjust things on the fly so to do that we're actually going to be using a power app so this visual here i'll kind of show you how it works first and we'll run through how we make it this visual right here on the right this is a this is a power up so in your visualization pain in power bi we have the ability to to include a para it's a native visual good okay kate's our favorite person everywhere so it's a that's a great name here we go so we have an option here where she's gonna she's got a sale here 20 2010 where we forecasted she's going to make some sales here you know it doesn't do a lot of power bi but has a few months here with the forecast so i'm going to say you know what in october of 2010 i actually think that this forecast might be a bit off i want to make an adjustment so by clicking on the on the month i can actually interact with this power app and say i think this is going to actually be you know i'm only going to sell 80 of what i've been forecasted i'm going to say you know power the i slow this month customer out of busyness what i can do is that this is now going to commit i'm going to write this directly back into our source data system so now once i make once i've refreshed this data source i'm going to see here so now we've actually adjusted this bar so the original forecast was the dot we've now had the adjusted forecast down to 80 percent of what we thought now this is a right back this is this is pretty cool stuff um on how to do this and write this right back to the source system so we're live updating data we're able to make adjustments on the data now in order to do this we have to build a power app and connect it to power bi so here i've just got a duplicate here we kind of have no power app we'll run through the steps of how we do this so the first thing we're going to do is just add the power up visual we can see here it works just like any other visual where we can resize it we can add data so it asks only has one field to kind of input here and ask what powerapps data would you like to input so i'm going to put you know the sales person i want to put from my day table our year month which is the um the the date that it happened and then i also want to put the product id these are the bits of information i'm going to pass to powerapps now you can see it's trying to do accounts because this is numeric i'm going to say don't summarize because i don't want the count of these i just want the actual values so now these are the three bits of data that i'm going to pass to powerapps that it's going to use to write back to a source system once i hit create new i'm going to say i want to build a new power app now it says almost done you know um you get to choose what tenant renault says we're going to take you to powerapps i'm going to click go to powerapp studio it's going to give me this very long url that it likes to write out and say i'm going to take you here so you can build your power app so i'm going to click ok if only those links were actually legible right so um i mean i'm in my test environment here but this is the point now i'm gonna pass over to anthony he's gonna he's the power-up specialist and he's gonna show you how we can use this information and build a power app from the information i've just passed from power bi let's jump over to the actual app here so one thing i didn't do a great job of as i was putting some of these things together is naming my labels my images buttons and so forth typically i don't really go in and rename those unless they're being used in other areas um actually a lot of these are being used in other areas so i could have went back and did that but just it being for demo purposes and doing it pretty quickly um i i was able to not i was able to get by without putting a bow on it so let's let's dive through that really quickly here so here's your power bi integration you're going to be able to see that um anytime you have an app that's going to be created by a power bi report it's going to automatically default that you're going to be using that data source so that's where that comes in it doesn't allow you to take that out it doesn't give you the option to do anything really with it but it does add that as you know one of your data sources for for an app all right let me see here i should change the screen there i'll see if that would help but it did not however here is a label i just you know typed in some information there change the the font and the the weight of it you know we're from what uh you know from milwaukee so i added in uh you know art museum i thought that was kind of fancy but uh being able to add images and things like that making it more personable i think is you know a huge piece that sometimes we miss out on if we're using the out of the box you know forms and such so being able to just add that small bit of personality to an app is is helpful then here's the button right below uh you know that's basically going to bring us to the next screen i like to have a home page on the majority of my apps depending on what you're doing i know uh steve and i in the past have have foregone using a home screen but um in this case i did so let's go over to the add new screen this is the one that everybody's familiar with so let me click on that um one thing that you're seeing here is there's a pretty small but lengthy uh you know query that we're seeing here so here's that power bi integration.data that's going to be pulling up through that information anything that you're clicking on on that report you're going to be able to see you know by pulling in the information by using that data source and calling it so what i'm specifically looking at here is the the year and month so that's what i'm pulling through it actually comes through as a year and a month so let me just go back to that power bi report to show you that so you can see here that there's 20 20 and then the month is at the end so the way that i'm going to you know extract that information is saying give me everything to the left but only the four characters let's only display that so that's what you're going to see there same thing with a month now pull everything from the right and only show me those two characters so that again just kind of you know further explaining some of those queries that you may have to do to help you know display that data in an end user-centric way uh our notes so again you saw me type in some notes that's just you know our area is i'd like to say as a catch-all um usually i have notes on a lot of my apps um may or may not be best practices i haven't really heard anybody talk about that much but what i like to do from uh i know this is my lean sigma hat where i like to understand how people are using the app and if they're gonna use it any which way that's possible great but what i'll start to be able to do is start to categorize on how they're actually using my app based on their input so um being able to see you know that they're categorizing it a certain way i can start to you know formalize a plan to maybe add another input put a category on here put a subcategory on here or make you know adjustments based on you know the information that they're putting in there again then down here below is going to be our you know slider bar so if i were to hold down alt i can then use it like i would otherwise be able to drag and drop that so i did adjust that a little bit to make that that bubble a little bit bigger you know making it easier on the end user again of course and then here's my my admin panel so if i click on that it's going to show you know those that information so again just being really high level here it really helps you know drive you know that that uh admin side of things sometimes it's just a validation check is it going to pull through my information that i wanted to is it going to submit the information that i wanted to so that just kind of bring in some more insight to that admin that uh so those end users may not care about so if i have submit here you can kind of see what i did here i'm just basically pulling in a collect uh i'm i'm submitting it to my data list and then here are is the list title and here's some of that other information that i'm pulling in from the actual app itself and that's where it's going to be stored so then when i click on that i want to navigate out to my confirmation page that's where i was talking about when it kind of looks like a receipt same thing here then we're going to be you know setting this up and uh you know resetting this once we're ready so now just to go to the confirmation page again we just have a label up here and what i did that's different here is i i basically just pulled in exactly what i was uh you know displaying on the other page instead of having to formulate the the properties twice i wanted to make sure that uh you know it's very consistent and in addition here if you guys caught that if you guys are or saw that earlier this only went to uh to 100 and it was supposed to go to 200 so you can see how i changed that very simple um and now i'm just going to drag and drop that as if i were using it if it lets me it's not gonna let me right now it's it's because i'm presenting of course so but in another case you can see that there's you know some length here on the right that's going above 100. so i made that that quick change then my button here is just navigating me back to that add new screen so if i wanted to publish this then you just go to file save wait a moment here of course and then publish and this usually goes pretty quick we'll allow that to spin click back so now that is going to show up here in my data report but one other thing i wanted to show you really quickly is the flow so here you can see that when an item is created do something and storing that information back to a list get the current profile the person that's submitting that information and now use some of that information in here but then also call out you know like we talked about with that receipt you know be able to um you know really pass along that information so that they have that in their records that they made a change um and again here's that that information about notifying your administrator typically you have you know something like an email there to for them to copy and paste into an email um but in either case you know that that being the flow so as you can tell it's pretty simplistic um if you're not a coder it's it's still something that was plug and play from like the title perspective i can do a quick one of those i'm just going to add a title really quick here so you can see how what that looked like so i'm going to i'm going to type in title just so i you know refine what is being shown so when a new item is added or sorry created that's what's you know being called here you know that because uh when we're able to pull in the title and it's to the data source because that's what's being pulled in there so i know that's the correct one i'm gonna click on title and it just pops it right in so again that's that low code no code very simplistic you know microsoft automate all right so if i were to save that it would save it and then anything that goes on beyond that it's going to pick up those latest changes just to give you some insight really quickly this is where it's being stored and being adjusted so this is where you're going to see that capture so typically we could have you know a bunch more pieces of metadata here if we wanted but for demo purposes just keeping that simplistic so now you saw all the working pieces refresh here real quick and as you can tell i'm a tab guy a million tabs all right we'll let this load and you'll see this load i got my thumbs up that will pop up here in a moment all right uh i only got a second of it so and this is not going to show unless you click on something so you're going to see me click on this and then it'll show so now here i'm going to go to 200 it's going to show me my 200 in a moment i'm just typing in some yada yada click the check box and now you can see that 200 and that change that came through key part here is that you know that it's the the data that he already has stored in his visualizing here in power bi and and he did such a great job visualizing that that information and being able to talk through that i actually learned a little bit during that that small discussion there so thanks to you but outside of that when you have a power app and you have data and you want to you know be able to visualize that whether it's at the same time or it's you know pulling pieces out to visualize it differently you know that's where that power app can really you know it can provide the interactive um side of things where you know an end user coming to this you know they don't have to leave the power you know pr power bi report in order to actually use that information they can just stay right here you can put the power app on there as an embedded feature and uh you know you can uh basically allow it to to do everything in one screen so i think that's really the big thing of you know what's how we're going to do productivity going forward you know we see uh you know power bi and power apps working really well together we see teams and power apps and power bi working together really well so i click on you know new adjustment so i could you know click on some other information that's in here and you know you know move around some information and we'll show that in a moment so i'm gonna click on add new adjustment so that brought me to a screen you can see 2017 is the year and then month is january so if i were to kind of click around we're going to be able to see you know some of that information changing if uh if the demo guys want to work with me here you can see that you know some of that information is changing around so we have 2019 kind of bopping around a little bit 20 20 and 11. you can see how it's interacting with the actual report itself and how the power app is able to pull in the information that the power bi has made accessible so one thing that's really great is that when you're in power bi and when you're in the power app if you have permissions to view power bi that means that you have permissions to view the data on the back end so the great thing here that you're seeing is that this power app is actually you using the information from the power bi you know parts itself and it's not actually connecting to the database on the back end so that's that's one great thing is that it's all stored within this you know this web front that it's passing these variables through and it's being able to do that you know very seamless and i think that that's one really important thing that we're seeing here is that you know powerapps isn't connecting to the backend data source and then finding out which one's selected on here and you know doing some type of match up um what we're seeing here is is is great because you know we're able to pass that information again you know right from power bi to power apps and it doesn't have to do these these uh heavy calculations to find those types of things out so as you're seeing here i'm just clicking around and you're able to see those numbers change which is great um let's just click on one here i'm gonna click on planning so it brings up the year 201901 and let's just say that you know we know that it's you know the adjustment here is going to be a little bit different than it was before for whatever reason the other in economic reason so let's say you know these guys they're going to be selling you know uh sanitation wipes you know right now i'm sure those were through the roof so let's just uh type something in here just as a note all right i'm going to make an adjustment i'm going to say 200 there we go and then i'm going to hit the the check box here so now you can kind of see you can kind of see what happened here thank you for your submission and then it kind of shows that information down below um one thing that i'm able to do is i added in you know a button here where it's going to allow me now to go back and make it another adjustment so i'm on this report i made an adjustment but i want to do that again you know i only did the one and i have you know a couple to go through and i don't want to you know keep opening up the the dashboard here so let's go in and make another adjustment just so we have that so again you know here's the planning one let's let's just change to fundraising let's say that that's going to go down a little bit i just heard a little ding and that just let me know that that power automate had you know finished up so one second here so i'm gonna let you uh i'm gonna share my screen here and uh you know have my video on so you guys can see me waving my hands around i talk a lot with my hands so uh so in the notes then you can see i added notes and then i'm gonna make an adjustment here i'm gonna say you know it's going to be down 44 based on you know my current findings i'm going to hit the checkbox again so you can kind of see that it kind of gives you like that receipt feel just says hey this is kind of what's going on this is how uh you know this is what you submitted and this is what you'll see in your inbox so i'm going to go ahead and go to my inbox pro from a power automate perspective now you can see that there's been some things that have changed so you can see here that hi steven a new adjustment item has been added and then here's the details so product name you know 1006 sales person my id the adjustment 80 and then you know and so forth so the the year and the in the month but being able to go through uh just if i think this is what i put in there by default it's just something that i've always done uh but being able to have something out there so that you can call out it's a better user experience typically i would have an email in there that would allow you know somebody to contact somebody to say hey you know i don't think that i was supposed to have this confidential information so uh certainly you know keeping an eye on on that and being able to allow your end users to you know work with whoever had created this this workflow or you know working with the data if there is you know data that's being escaped here we want to make sure that we can see that so this was from an earlier one when steve had ran through it here's the one that i first did and you can see the 200 there and here's the one that i did uh as a second round so you can kind of see some of those numbers come through um as you could tell you know power automate it's not an instant thing it you know it'll take 10 to 30 seconds you know that's something that to be expected so now that you saw the power automate there i'm going to go back to the actual report and now i'm going to pass the the ball back to steve and he can do his wrap up here thanks a lot there i just sent myself on live so let me get let me get that off thank you here we go i've got my screen again now so yeah it's really good a lot of information i think here so i really want to just kind of summarize everything we've done here let's talk about all these different moving pieces so really what have we what have we done here we started off with some data in a dashboard we then you know built in this power up and we passed a few bits of information into this powerapp directly from the dashboard so i've got here again you know we can filter the the information here just like we would with any other power bi visual it will filter the data that's been passed to the power for example i've got here you know we're named for our sales person is an act and again if we had row level security that would work in the same way and this would filter the information and pass the filtered information you know to our road level security through to the power so our users couldn't be adjusting other people's forecasts i'm going to drill down into retail and put a filter here now i'm going to say my product i think i'm going to pick is is going to be banana so as and i'm saying you know i'm i'm filtering the sales person to ant the product of banana passing that through and i'm going to select a month that i really want to filter here 2011. i'm going to make my adjustment i actually you know what i think i'm going to go 30 and say donkey kong on the loose stole all my bananas now this is something that our ai um reckon is our ai forecasting didn't pick up he didn't realize donkey kong was getting loose and still up and honest so we have a supply issue and i really want to adjust this now what actually happens when i press this button is that as anthony designed this power it's going to save this information into the into an adjustments table and once i refresh this adjustments table we're going to see that this bar went down and i can hover over and i can see you know i feel a little here and said we've had an adjustment because we think our forecasting you know models are good but it can't capture everything so our salesperson and has gone in here and said in november donkey kong is on the loose and he stole all my bananas i'm not going to be able to sell as many bananas here and made an adjustment of you know down to 30 so this is this is um very powerful stuff and i want to just kind of go over a bit of architecture here and show exactly how this is working so really what's happening with all these products how are interacting how is it writing back to the data source so what we have is we have our different data sources we in our case we used um some some machine learning this could be a machine learning model for our forecast we had some information in our databases about the sales we've recorded and we've also been using a sharepoint to hold an adjustments table sharepoint might not be you know the best method probably a database but for our reporters it's easy to download the sharepoint so you guys can see it and we can show what's happening in real time and these will filter into a power bi report so you know typically nothing here unusual we're just filtering to a power bi now what happens when a user goes and interacts with this power vr report they apply some filters that's what power bi does so this could be row level security filters it could be us selecting the product banana and are selecting which month then this data the filtered data is passed to paraps from power bi so this is an important thing to note here the power apps is accessing data directly from the power bi report it's not reading from the data sources that the power bi reports reading directly whatever we have loaded and passed into that visual on the page that's the information that's passed to the power app so once we look at our report we interact just like other visuals just like it works with any visual we make a bar graph or anything like that the information is filtered and past their visual it's the same with our power and then this is how kind of the magic happens once that information this filter table is past the power app powerapp reads it in something called power bi integration that's the source that it uses and then this actually saves it back to anywhere we want in our case we want to save information back to our adjustment table which then you can see how this works we have a circle here so we see all the information we have information from other sources loaded in we apply some filters this table this information gets passed through to the power app we then in the power app add some more information in this case we added some notes and an adjustment number the power app then saves this to a data source this happens to be you know the adjustment data source which is the same one we're using the power bi report of course if you're using a sql table you can have a direct query so you wouldn't have to refresh the report every time you wanted to see live updates and then of course it's possible for the power app to do other stuff we called a flow to email us now this could be really important you might not want to email you know yourself it could be an email to the manager saying hey one of our sales people has been looking at the data they've applied some filters they've made an adjustment they think that our our forecasting's wrong and they want to input some information and apply an adjustment so they can do that directly in the power bi through the powered app interface and as well as saving this back it ran a flow you ran another action i actually just emailed but this could be anything besides an alert it could be an approval process really the power is these three different products interacting together you

Channel: Milwaukee Power Platform User Group

Views: 12,615

Keywords: Microsoft, Power Apps, Power Platform, Power Automate, Power BI

Id: 7KppXpVoEYM

Length: 35min 14sec (2114 seconds)

Published: Sun Aug 30 2020