Write Back in Power BI with Power Apps Visual | Input Data from Power BI Tutorial

hello everyone reza here in this video i will show you how we can write back data from power bi directly to our data source we will leverage a power app directly inside the context of a power bi report pass fields from power bi data set to power apps and the power app then will go ahead and connect to our data source and perform update add and delete operations i will also show you how we can refresh the visuals of the power bi report directly from power apps so let's check it out in action [Music] power bi allows us to analyze or visualize our data we can create dashboards and reports by connecting to any data source no matter where it lives on premises or in the cloud here i have a power bi report the data source for which is a sharepoint list the list is an issue tracking list that tracks issues that users report we can track the status of the issue and plug in the notes as these issues are closed now with power bi i was able to build a report that visualizes the data that's coming in from that sharepoint list i can visualize the number of issues by the region from which they've been reported on the number of issues per status per type and more power bi data sets are read only however what if i would like to write back data directly from power bi to my data source which in this case is a sharepoint list since power bi data sets are read only in order to write back from power bi we need to take the help of another service of the power platform which is power apps with powerapps i can act on my data perform crud operations create read update and delete operations on my data sources so let's try that out right here inside the power bi report itself by bringing in a power app visual i will edit my power bi report and from the visualizations we have a power apps visual for power bi i will add that visual the first step is to pass fields from the power bi data set to the power app which is living contextually inside the power bi report for my issue tracking data set that's connected in the power bi report these are all the fields that are being exposed from my sharepoint list the key thing here is to always pass the primary key of your tables in your data sets for sharepoint the primary key is the id column so i'll go ahead and select that i would also like to pass some additional fields i will pick the priority the region the status the title and the type so i've selected my data points that i would like to pass from my power bi data set to my power app and the moment i do that the visual transforms itself and provides me two options to choose from i can either create a brand new power app or i can choose an existing powerapp in this scenario i will create a brand new powerapp and you can also select the environments in which you would like to create these apps in i will build this in my default environment and i will go ahead and click on create new it will redirect me to the powerapps studio experience wherein it will create a single screen power app that would have a gallery control which will have its data coming in from the power bi data set here's the app that has got created i have a gallery here the items property for which is coming in from power bi integration dot data this object called power bi integration is a special object that gets created when you create the power app directly from that power bi visual or you connect to an existing power app from that power bi visual for this gallery control the data is coming in from my power bi data set i have a label control here that is showcasing the id field that i passed from the power bi data set if i change this to this item dot it will list out all the fields that i selected to pass from my power bi data set to my power app so if i pick title it will showcase the title of my issues and since this is a gallery control i can change the layout of this control and showcase additional information for example i'll select title subtitle and body as the layout here i have the title of the issue i will showcase the status of the issue the priority of the issue and since this is a power app i have complete control over the ui of this canvas app let's go ahead and insert a simple label control and i've gone ahead and added some simple formatting options to make this label control appear like a header for this app here i've added another label control to showcase the type information once i have my landing screen built out i can go to file give my app a name and save this app so that this app gets saved and published to power apps once that is done if i go ahead and close this browser tab and back to my power bi report i will go ahead and click save and refresh the web page the issue tracker appears showcasing all this information that is coming in from the power bi data set the beauty of this is if i was to filter my data in power bi the information showcased in the powerapp will also filter itself i'll apply a filter on the type property type 2. the moment i select this if you observe the power app it only shows the issues related to type 2 let's add another filter show me all the issues that are type 2 and the priority is low we can see how the power app is respecting the filter experience also if i was to filter information my report by selecting information on certain visuals for example here i've selected north america so i've applied the filter here for the region north america and then within this i want to only look at the issue 28 and 36. as i make these selections you can see how the power app is respecting those selections let's try and see how we can perform crud operations on our data source directly from that power app once again i will edit the report since i want to make changes to my power app i will head over to the three ellipses here and go to edit this will once again take me back to the powerapp studio experience here i want to create a screen which will allow the user to make updates since the power bi data set is read only if i was to try and perform update operations on this data set i will receive an error stating that this data set is read-only however in powerapps i can go and connect directly to my data source so i'll head over to data go to add data and create a connection to the sharepoint site and connect directly to my sharepoint list now i have my sharepoint list connected in my powered app i'll create a form based screen so the user can interact with the record in sharepoint and perform the write back operations and for that i will head over to new screen we have a template called form i will select this here i have this edit form control that i will rename to my issue tracker form the data source will be my issue tracking list that is connected directly in my power app this will list out all the columns in my data source i can head over to edit fields and choose the fields reorder them design the form based upon my use case for this form control there is an item property and this is the item or the record in your data source that you want this form to load the information for and that will come directly from the selected item of the gallery on my home screen now the gallery is getting its data from the power bi integration object the form is connected directly to my data source so the key thing to do here for the item property is to perform a function here called lookup lookup returns a single record from a data source depending upon the condition that you define look up my data source which is the issue tracking data source that's connected in my power app where the id column which is that unique key in my sharepoint list is equal to the calorie on the home screen dot selected dot the id property that's one of the fields that i selected to pass from my power bi data set to my powerapp visual so i will select id now the data that gets passed from power bi to powerapps gets passed in the form of string id is a number type column in my data source so what we will have to do here is typecast this using the value function so now this form will load the data depending upon the item that i select in the gallery on my home screen for the gallery on the home screen if the user selects this icon i will go ahead and navigate the user to my second screen let's preview the app let's pick issue number 11. i will select this icon it will navigate me to the second screen and load all the information for that specific record directly from my data source which is the sharepoint list when the user clicks on this check icon it goes ahead and submits the form so the information that is provided in this form gets submitted directly to the data source and the form control has a property called on success so when this is successful in that case i would like to navigate the user back to my home screen which is screen number one and before doing this i would like to give a message so i'll use the notify function to state that the issue was updated successfully i will display this as a success message for two seconds and then navigate back to the first screen if the user clicks on this x icon on the top once again i would like to navigate the user to the first screen and the title label here i will call this my issue form let's try this out i will select x takes me back to the home screen let's select issue number 11 let's add some sample notes and click submit this will submit the item to my data source it says it's been updated successfully and for issue 11 if i get back to my form i can see the updates are being held so i'm able to perform a ride back directly to my data source which is my sharepoint list now the data on the home screen is coming from the power bi data set let's take issue number 10 the status is blocked i'm going to select this i'll change the status here to pending now i'll plug in some notes and click submit now even though i did that if you observe the gallery on the home screen still shows the data as blocked for issue number 10. but if i go to this specific item the status shows as the update that i made including the notes the reason is because this form control is connected directly to my sharepoint list through the power app so it's a live connection to my data source whereas the data on the home screen is coming in from the power bi data set the data set for power bi is sharepoint and when you connect to sharepoint as a data source in power bi it does so in import query mode that means you have to perform a refresh on the data set in power bi in order for power bi to showcase the updated information from your data source and here is my data set which is issue tracker now i can manually initiate the refresh or i can schedule a refresh of this data set the number of refreshes that you can perform depends upon your license i believe you can do a maximum of 48 refreshes if you have the premium license per day and it refreshes if you have the pro license there are also ways to initiate that refresh directly from the power app as well but bear in mind you are limited to how many refreshes you can perform in a day so that is a challenge that you're going to face in scenarios wherein your data sets are in import query mode and specifically we are talking about sharepoint here now since i've added a new screen and made changes to my app i will go ahead and save this and publish my app head back to my power bi report save there would be caching that would go into play so it does take a few minutes before the changes that you've made in the app reflect on the visual in power bi issue number 14 that says spending i'm going to select this i will add some notes and submit the update was made if i was to go back to issue number 14 i can clearly see the updates i will go ahead and update my notes and change the status to completed and submit once again it is successfully making the update in my data source however issue number 14 the status here still shows us pending and that's because the power bi data set has not refreshed let's initiate the refresh manually in this scenario i've clicked on refresh so the refresh process has started and the refresh is complete this time when i launch my power bi report issue number 14 the status is now completed so let's get back and add the ability in the power app so users can create issues or they can also delete their shows i will insert the add icon on select of this i will set the mode of my form to a new form by using the new form function and providing the name of my form control and then i can navigate the user to the form screen now since i am changing the mode of the form prior to navigating when the user was selecting an item in the gallery i was only navigating to the screen now before this i will ensure that i set the mode of the form to edit by using the edit form function let's preview the app i click plus i can create a new issue i select an existing item it will show me the data for that specific record and for deletion i will edit the gallery insert the delete icon and on select of this i will use the remove function on my data source which is issue tracking and here i need to give the context of the item that i'm trying to delete very similar to what we did for the form controls item property to get the context of the item i'll literally copy this code for the remove function the item property would be the same as the item property logic of the form control and once the remove is performed i would like to notify the user made updates to my app so i need to ensure that i save my app and i publish my app back to my power bi report let's go ahead and create a new issue i've gone in and plugged in the information on the form i click submit the item gets created directly in my data source that item will not show up in the power bi report until my data source gets refreshed let's go ahead and delete an issue ratio number 10 i will delete this it has been deleted from my data source but it would still show up right here until the refresh is performed i've initiated a refresh of my data set this time when i launch my power bi report all the latest updates would come in play issue number 10 no longer exists i had created a new issue with the status new so let's directly filter to all the issues that are in new status and here is the issue that i created which was the phone connectivity issue now you can take this to the next level if your data sources support direct query here's another power bi report that has a power app living in context however this time my data source is microsoft data verse that supports direct query so the data set here is in direct query mode which basically means it will query the data source directly to give you a near real-time experience the data set here is focused around devices that my organization has procured let's take lenovo as an example so i select lenovo in this visual so it has gone ahead and applied that filter to all the visuals and we can see that change in power apps as well for lenovo all the devices are currently available let's go ahead and make a modification to the status of the device i will select the lenovo s300 this takes me to the form experience very similar logic i've connected this directly now to the dataverse table inside of powerapps so i can make the updates here i will change the device status to assign this time though when i click on submit something different is going to happen it will still perform the update directly to the data source which is microsoft dataverse in this case however since the data set in power bi is connected in direct query mode the update that i make will immediately show up in the power bi report experience and to see that update typically you have to refresh your power bi report in order to refresh the visuals however the power bi integration object in powerapps has a special property which allows you to initiate that refresh directly inside of powerapps so let's check this out in action the status is changing to assign if you closely observe the visuals here you will see lenovo having a new bar show up here for the assign status live i'm going to select submit for lenovo s300 it goes ahead it makes the change and now it is refreshing the visuals in power bi lenovo i have one device now where the status is assigned and the power app home screen which shows the data from the power bi report is showcasing that update live the device acer aspire ma is currently assigned i'm going to select this item and change the status here to in repair i'll click submit power app will now refresh the visuals and if you observe currently there are four that are in repair this visual now will update dynamically on the fly live and we can see that number now changing to five with power apps you are free to perform multiple data operation types for example bulk updates five of my devices are currently in repair and if i apply a filter on my page to only showcase the devices that are in repair that change will be reflected in all the visuals including the power app i want to change all of their statuses to available i will switch here to bulk update mode select all of these and change the status in one go to available and select update this will perform the patch operation directly to my data source table which is microsoft dataverse and powerapps will initiate a refresh of the power bi visuals we can see the changes live in action there are no devices now that are in repair the magic here is that powerapp can refresh the visuals of the power bi report and because the data set is connected in direct query mode the experience is near real time and to perform that refresh operation from my power app every time an update is performed against my data set i am leveraging this extra function which is called power bi integration dot refresh this will refresh the visuals of the power bi report i perform this refresh when the user deletes a record and i initiate that refresh even when i perform the bulk update if you enjoyed this video then do like comment and subscribe to my youtube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 102,882

Keywords: write back power bi, write back option in power bi, write back power apps, power bi input data, power bi input data from user, power bi power apps, power apps visual, power apps visual for power bi, reza dorrani, input in power bi, power apps, write back to database, write back from power bi, power bi, power bi tutorials for beginners, add data power bi, update data power bi, write back data from power bi, power bi refresh data, power bi integration, write back, writeback

Id: 5y2jJyH4Eu0

Length: 24min 6sec (1446 seconds)

Published: Mon Apr 11 2022