Instant Data Magic: PowerApps in Power BI using Dataverse

what's up everyone if you're tired of having just a plain old static dashboard well there is a very simple way that we can actually make updates in real time do data right back have it show up with the click of one single button and update all of the visuals in your dashboard just like that I'm going to show you how to do it we're using power apps and we're using dataverse so let's jump right into it here we're going to start off with a little bit of theory just so we're all grounded on what we're actually doing behind the scenes in this tutorial so we are doing data right back with an embedded power app inside of our powerbi dashboard this works for any and all use cases we're going to be doing it in the personal net worth tracker tool that we've built in a previous video linked above and what we are doing differently this time is we want that realtime data update we don't want to do a schedule refresh we don't want to mess around with data gateways or re-imports things like that so what we're using behind the scenes is a database called the Microsoft data verse it was earlier known as the common data service they they renamed it as they do sometimes and what that allows us to do is it allows us to query with the direct query method in powerbi I'll show you how to do that so that we can have that single click seamless right back of data so let's jump over to powerbi so the basis of where we're storing our data like I mentioned it's called Microsoft data verse and what it is is it's sort of a lightweight Cloud backend for a lot of the power apps infrastructure that's been put out there so depending on your license if you have Microsoft Office 365 for business and uh the power apps add-on for that you do have the ability to create your own dataverse tables so that's what I've done here um in power apps.com you have all of your apps as well as this option here with tables and every table here is a data verse table this is not going to be an in-depth tutorial of Microsoft data verse but what I am going to show you is that you can create your own table a custom table and you can actually import any table from uh Microsoft Excel and you can mimic stuff that you maybe are doing in a SharePoint online list the concept is just like Excel grid like rows columns and with different data types for instance here this is just a text field and we have a whole number integer field we have a choice field here but this looks just like the data we've used in previous videos there's only a few rows three are hidden here you can edit in this space but all we're doing is we're providing the backend for our app and the reason it's dataverse is is because we want that real-time update so let's go into powerbi this is the net worth tracker uh with fake numbers we've seen it all before so in power query we have one table of data being brought in it's the same table we just saw in our Microsoft data verse and I'm going to give you a very quick tutorial here on what it looks like to bring in data from Microsoft dataverse and the very important step of selecting the direct query as opposed to the import so in Power Platform data verse is considered part of Power Platform like everything else we're doing in this tutorial you click connect there and you get a list of your environments and then you get a list of all of those tables that are part of your dataverse tenant so in my environment there's a lot of system tables I have no idea what all of them do and I'm just gonna ignore all of them and scroll on down to my new table the custom table that I made uh which is just called new table and if I select that just like with any power query bringing in data you get a quick little visual here and there are a lot of system generated Fields but we'll clean that up very quickly at this point you are getting that option here and then you're even given a little hyperlink to learn about direct query so what direct query is is there is no importing of the data right there's no um pulling in of all the rows in a query it is directly caching in your browser against that data source so that's what enables the real time update whereas an import you of course would need to update the semantic model behind the scenes of your powerbi report doing things like a scheduled refresh you know with the eight times a day limit depending on your powerbi license so I've got direct query clicked here you got to make sure you click direct query walking through some simple power query steps here all I've done is I've removed the columns that I don't need you do have some kind of interesting stuff in here like uh that are it's system generated it's automatically doing this for you by using the data verse uh as your table behind the scenes so created on modified on so you could do some auditing and tracking of uh users depending on how big your data set is or or what you're using this technology for so simple remove column steps and then a rename of columns and now we're ready to hop over into powerbi at our normal design canvas this is the net worth track r view we've had and of course what I want to do is I want to create a way for us as users or maybe a client to change or enter new data and have it update either our personal assets or our liabilities have it update in real time there's a little tip here that I do want to point out it's when you have a direct query table coming in like we do you do get this little color uh bar on top and then if you hover over it it says storage mode direct query and shows us that we're connected to Microsoft data verse otherwise known as Dynamics 365 which is like Microsoft CRM um or and Erp uh offering so that's all working and now let's publish this to the service so that we can create our power app visual oh and if at any time you want to download this PBX template and then follow along all the steps exactly you can do that there's a link down below for uh the template download and notice we have hopped over into the service and the reason we are in the service is because this is the best practice way to add in the integrated power apps visual so over here click that power apps visual it normally automatically comes in like a iPhone sort of orientation and you have some options here but the first thing you need to do is you need to drag in those fields onto the visual just like you would do with any other powerbi graph or Matrix visual so I'm going to grab um asset balance the ID and the type here and at this point it's going to prompt us to either create a new app or choose an existing app so in this case we do want to create a new app after clicking on create new app it pops open a new tab in our browser and this is the power apps creation area and some of these steps are going to be duplicative with my previous video where we use SharePoint as a back end but in this case we are just going to start from scratch so that we learn it all together and what it automatically gave me is something called a gallery and a gallery you can liken it to it's like a visual that you can make it look similar to an Excel table but it it provides a list of the records that you have and over here on the right you get an option to select different layouts so you could have things like images or title and subtitle what that did is it um did a title of our asset and then a subtitle of the ID from the data verse table so I'm going to maneuver these around a little bit you can go up into the insert area here and you can add a text label and this can be your title I'll just call it accounts I'll Center it I'll adjust the color the background here so I mean you really have a ton of different properties you can play with uh either here or up here in the formatting dropdown but of course what we want is a record of the balance so make sure you have your G Gallery selected and then go insert into a text label and instead of ID we want this item sum of balance and that'll return the balance of our different accounts and liabilities you can resize it there do a little formatting I'll save some time and not show that on the video doing a couple bits of formatting here in the text area we can make this look prettier and more like currency so this is similar to an Excel function or a Dax function I'll do text and then if we do I think that's what we're looking for press enter and then we get that nice comma format with the dollar sign there so at this point we need a new screen just like you would have in any app on your phone and I'm going to go to templates here and we want a form and what a form screen is is it's all about updating that record so a form I'll just call this form screen we want to make sure we connect to data so clicking this uh data little Silo over here and on add data and it automatically knows that I actually want Microsoft datae so if you connected to a SharePoint you could type that in here connecting to a one drive file type that in here or a SQL Server so I'm going to click my table here it's going to make that connection and on the data source for our actual form which is an item just like a gallery I want to be able to edit multiple Fields not just account and create on in fact I do not care about create on that's a system generated thing for add field I of course want to be able to change the balance of my particular account and I do want to call it either an asset or a liability so that's what that account type is there and I'm going to reorder this a little bit so if you just drag you can bring your Fields lower or higher and you can also do that in the little ellipses area here before I clean up all the formatting and do different titles and things like that I want to talk through the mechanics of what's actually happening on this form page so first things first let's talk about page navigation when we created this form page it gave us an X here and a check mark here so of course the X means let's just give up and go back and then the check mark should mean save the changes that I put into this form so starting with the easiest one first the on Select Property of the X we just want to nav navigate which is a function in power apps navigate back toque screen one screen one screen one that's going to take us back to like our homepage and to test things you can always hit the little play button up here and x and it took us back to the homepage so heading back to our form let's click on the actual form and let's adjust a property known as the item we want to use a lookup function here so we know exactly what record we're looking up the name of our data verse table is that net worth trackers and we want it where our account is equal to that Gallery one selected so whichever record we clicked on that first screen wherever the asset is equal to that so closing that up and it read that all the way through we had that that one selected and we can also eliminate this table ID down here if that's not needed so now let's talk about the distinction of what actually makes this real time versus what takes a scheduled refresh or refreshing your powerbi data set up here we've got a simple submit form and of course what that's going to do is it's going to update that record in your data verse table remember the gallery on the front page is integrated with with your powerbi data set and then our form is integrated with network Networth trackers so if I test it here and I change the balance by adding a dollar to the 401K save successfully I have to go over here and if I refresh the visuals because we've got that direct query that will update the number and bring that 8,4 over here I want to take it one step step further I don't want to even have to click this refresh visuals button so what I'm going to do is in the on Select Property here I'm going to put this function here called powerbi integration. refresh and what that does is it refreshes any direct query uh data source like what we have with our data verse table so if I do that and I press save and I make sure that I also publish we'll test it out in our normal webbased powerbi report like anyone can access I'll go to that 401k I'm going to make a big change here and I'll press that checkbox and we saw that all of our visuals then updated they did the little spinning thing and that powerbi integration. refresh function makes it real time so that's the distinction there now taking it even one step further not just editing current rows or current accounts that we already have in our table we can create a new account in this case it's our massive stash of gold bars they are assets and got a million bucks there and we can see the real time update here and there in pink are the massive stash of gold bars so lot of different things you can do here I mean tons of tons of you use cases for all sorts of different modeling or whatever you're doing and if you have any need for this template free download link below and you can watch that video as well that that Link's in the description so we'll be making more videos probably weekly maybe bi-weekly who knows and thanks for watching [Music] he

Channel: Ptarmy

Views: 3,402

Keywords: stock analyst, financials, research analyst, powerbi, powerquery

Id: jES13Tv2-xk

Length: 16min 2sec (962 seconds)

Published: Sat Feb 03 2024