Using PowerApps Visual in Power BI to interact with data 📈 (2019)

hello everyone and welcome to this video today we're going to look at integrating power X and power bi and I use the word integration because by default these are two completely separate services and there's actually very little that that joins the two so what we're going to do today is have a bar be our report that uses direct query so it'll be able to refresh or show you the latest data as soon as you refresh we're gonna have a power app that's gonna be able to read information from power bi and call the Refresh commands on power bi in order to show the latest information so thanks for watching and I hope you find this valuable first let's have a look at the data that we're going to be working with and as you can see there's a very simple table called expense claim and we've got four columns in this table so we've got an ID which is the primary key expense diet expense amount and then expense category so the primary key is very important the moment you update data from power apps you do need a primary key and if you don't have one you simply won't be able to update the data source it'll give you an error I think the exact areas it actually puts the the data source in in the read-only mode so it might set you off on a wild goose chase thinking that it's an access context s issue or security issue or something like that where it does need a primary key if you're gonna update the table right let's have a look at our report again it's a very simple report just to show you how this works and we've got the latest version of the power bi desktop which is brilliant if you haven't if you don't have that yet please make sure that you get it and there's also a few neurons myths in the way that you can interact with these perhaps from the reports using the latest version of the desktop alright so let's go and make sure that we've got the visual you'll see over here we've got a power apps visual already loaded if you don't have one loaded yet you can go and click on the ellipsis say import from marketplace and then from here I can search for power-ups so you can go and search for power-ups and they we've got it over there you can add it from here as we go and add it to to you to your power beyond a stop right once you've got it on on board you can go and just click on it and then you'll see that it positions it in a phone layout view so but default the perhaps that barbie i cryed will be in the file art you can jump through a few hoops to get it into a tablet layout if you really wanted to but for the purposes of this demo I'm just gonna keep it in on the on the phone layout first thing we do need to do is tell that this visual what data we're going to be sending power apps from power bi now these two services are these two services I mean power bi and power apps really are completely separate services so what's happening here is tell power apps what dates are we're going to be working with so to do that we're going to go to our expense claim data set and then we're just going to drag these across into the power apps visual over here so you can actually just go and select them as well that works as well something to keep in mind over here is is to check for if you have any IDs to make sure that in this visual they're not counted that can really really cause havoc and allow will cause you to update incorrect data so you're thinking you're working on record but perhaps actually under the impression you're working on a different one because it's adding and it's calculating the IDs so let's make sure that that's not happening as well as any other amounts so just gonna keep all of that simple to to not summarize all right so that's looking good and essentially we can now go and tell power bi to create the bar for us you can choose an existing app just a would a warning if you would like to automatically refresh report as soon as data updates in your power up you need to create an app from the table on desktop or from the part behind service as well in previous versions you had to publish the report to the sir power beyond service before you could create the app now you can actually just create it from the desktop as well and it works very well but you need to create a new app if you wanted to refresh the table report if you choose an existing app and it is an app that was created from the report then it'll be able to refresh as well if not if you just use another power app something that you created completely outside of power bi it doesn't have the hooks installed into the bar app in order to talk back to power bi so just keep that in mind and I'm gonna make sure that you plan it correctly just don't one other point that I forgot to mention is the power bi report that you see in front of you users directquery and that allows us to automatically refresh the data immediately as soon as the the data changes in sequel if you run these queries in import mode then you have the the limits on how many of all then you have to actually do the refresh on a schedule and then it doesn't quite give you the experience that you are looking for all that we are looking for in this instant all right so let's go and tell it to create the power usually it takes a second and then it comes up with a pop-up with a warning that it's gonna redirect you and then typically that happens with our issue like most things on power apps and there is our brand-new power first thing you'll see that's different is on the left hand side there is an object called power bi integration and this is how you know that you are heading in the right direction so this is the thing that makes it rustic receive data directly from the power bi service as well as sin c'mon still it like refresh for an example so what you not do with aspera from young forward really depends on your purpose for the integration so if you wanted to just have the ability to add new data then you'll design a certain time per app if you wanted to be able to just edit individual records it'll be another fara in our case we're gonna have one with a gallery we can actually browse through the records that that it after it is being filtered by power bi and then also select a record edit that record as well as adding a new record alright so let's start by implementing good practices and renaming our controls to something that'll make sense 3:9 the gallery to Gail well CIM and our screen to welcome screen right next we want to just make this gallery look a little bit better so it's going tell it that it should use a heading 5 and let's give it a full if you don't talk or GBI you can just go and say light grey right so that's looking good let's go and remove that separator line and and then I think that should be good for now we also like to just at the moment all of these arrows or showing so let's go and set it so that only the one shows that's currently a further for the item that's currently selected so we're gonna go and say change the visible property for that to this item dot is selected and if you now run this you'll see that that's doing what we want it to do right so let's just add more data so which is just easier to see what are we working with so to add a label and you'll see that it now automatically picks up the fields that we post it post a visual from RBI so if we're gonna say expense amount which is what we're looking for in this example for now I'm just gonna put it down there alright so now let's add another screen and choose a form template screen and it's rename this to edit screen let's first connect this to data right going back to the gallery you'll see that in the items property for this gallery it's currently pointing to the power bi integration which is this object over there at dot data and that heart gets the data at this point in time this bar app is not connected to any other data sources whatsoever so if we wanted to this bar app to also update the data and not only browse the data we'll have to also add the data source so on the new data source view on the Left let's go and go to connections we can scroll down to sequel select the sequel database and then if you don't have a connection you'll just have to click on all connections and then add it so we're gonna add the connection to that table there we go so that's added successfully and you'll now see it at the top under in your app so let's go back to our tree view on the left and then go back to our form edit form you'll see that at the top the data source is currently set to blank and we want to go and change this right so we want to change this to our new the sequel server or the sequel table that we've added and we can also now go and edit the fields so we just want to display you don't need to select the key as well perhaps clever enough to know what you what you're working with so you don't have to display that on the form as well right so here we are pretty much good to go the only other thing I would change at this stage is an item while going select the welcome screen dot selected so this is if we're editing an existing item then we want to work with the item that's selected on the Welcome gallery and even though it's not in the same data source the gallery is looking at the power bi data while the form is looking at the sequel database but it's clever enough to know that it's working with the same record around because of the primary key right so let's have a look at the top and firstly it's gonna give this little X something to do so for that we're just gonna say we want to navigate back and we want to use a fire transition okay next we want to go and have the submit button you'll see that on them at the moment on select submits the form and for this that's fine we we don't want to change that it still needs to do that but now we want to do something in addition to us so we're gonna add a semicolon and hit shift enter and now we're gonna cool the power bi integration dot refresh which is going to just refresh the report or the data that you're looking at and then at the same time after this we want to navigate to the welcome screen and we also want to use the fade screen transition so let's rename this to something I'll make a little bit more sense so say see submit and now we also want to hide this so we want to say this is should only be visible when the Edit form is in edit mode so if we go and say edit form dot display mode equals display mode dot edit then we want to show that submit button and obviously if it's not in edit mode we want a a way for it to to be put in edit mode so let's just create a copy off that icon so I'm just going to copy and paste that it's gonna move that in place gonna rename the icon to edit we're gonna change the visible property of this to say if it's not edit then we want to display the I say edit and at the same time we want to change the on select statements at the moment this is changing the the form with submitting the form we want to change this to go and say edit the form so we want to edit form and the form that we want to edit it happens to be able to form one alright and that's all that we need for the edit icon except for the fact we might well have changed their icon that it displays and this is pretty new where we can have select a different icon in the same icon control alright so we've selected that for the Edit icon it's not displaying at the moment because the form isn't currently in edit mode we might just want to rename this title or just actually delete that we don't need it for this so we've got everything that we need on the Edit form now so we can head back to the welcome screen and every year we want to do two things still so we want to add an icon so if we go to insert icons let's add a little plus sign and this will allow us to add a new record and unselect we're gonna say new foam and the formula we wanna cool is edit form one and then we want to navigate to the Edit screen and we want to use the cover right transition and that should work well it's gonna copy the transition over the navigate because I'm gonna use it just now as well and I'll be using that in the gallery just so what happens when somebody selects one of these items so let's just go into that little icon over there and say currently that said to you when that selected it should select the parent and if I have a look at the hard rock it's gonna select the gallery so now on the gallery we can say that on select in other words when something is selected we want to first make sure that the form is in V you might so we're gonna say view form and then edit from one semicolon and shift into enemy line and I'm just gonna place that navigate I come on in there to go to that screen so at this stage let's go and save the app and also publish it it's important that it should be published in order for the changes to reflect and if I go back to my par be our report you'll see that it is showing you the latest version of the bar app which is amazing some of the stuff done work quite as nicely on the desktop though so just make sure that it is published so I'm just going to publish this to a workspace online and now launch it directly so let's gonna have a look at the data and from first glance everything seems to be in order it's gonna be look if what happens when we slice and dice this info you'll see that as I select these categories the bar app responds accordingly and this is because the gallery is actually showing the data that is sent to it from the power bi reports through that RBI integration object which is quite cool so filtering the data is only part of the trick let's go and add some new data it's going say 4,000 Rand I think subscriptions are running a bit low so it's run 4000 around for subscriptions let's get the spelling right and it's against a fourth of August and it's gonna save this and the data should automatically update and there you go so all of that is working exactly as planned and I'm sure that you'll be able to use this in your projects going forward thank you for watching please let us know if you have any feedback or any questions or suggestions and if you haven't subscribed yet please do so and comment and like and share tell your friends and hope to see you in the next video have a good day bye

Channel: Dave's Tech Tips

Views: 44,644

Keywords: Microsoft Power Platform, power bi powerapps integration, Refresh, microsoft powerapps forms, PowerApps, Access, InfoPath, power bi powerapps, SQL, Power BI, Visual, microsoft powerapps for beginners, Microsoft, Development, Direct Query, microsoft powerapps

Id: b9gBXUdd55M

Length: 18min 23sec (1103 seconds)

Published: Wed Sep 04 2019