when you work in excel it's easy to make changes to the data when you for example have a table like this one over here and you want to change a value or add a value you just double click on the cell value enter the value and that's it however that doesn't work in power bi because power bi only reads from the data sources you cannot write back to the data source however there are of course workarounds and one of them is to use powerapps to write back to the data source and that is what i'm
going to show you in this video [Applause] welcome to how to power bi my name is boss and if this is the very first time for you visiting this channel then make sure to hit that subscribe button if you want to stay up to date on all of my videos in which i share everything i know about power bi now let's have a look at the table visual that i built over here where we have the client id client name subscription status and cancellation reason and what i want to be able to do is to
change the status and the reason and at the moment well i cannot do that i cannot just double click on a cell and add it or change the value however with a little workaround using powerapps you can now the powerapps visual you also find over here in the visualization section just add it to your report and now you have to add the fields that you're going to use for this app so let's go here to my clients table and here i want to have all four of these fields and now we can either choose an
app or create a new one no we did not build our app yet so let's create a new one now you get this enormous url just click on ok and it opens up your browser so now we are in powerapps and we're going to build an application that lets us write back data to the data source now here on the left hand side you see all of the different screens that we are going to build for this application and the objects that we place on these different screens and you see that automatically it already added
here first screen with a gallery that contains different items now what is also important is that we have here power bi integration so there's a special connection between your report and this app and we can only use those fields that we added in our power bi report to this powerapp visual now let's make a simple change i'm going to select one of the items and there you see we have this item and what i want to show the cancellation reason well this was chosen automatically but we don't have cancellation reasons just yet only the test
one over here at the bottom so i'm going to change this to let's say the client id so now we have all of the client ids but we cannot do much with it just yet however i want to see if it already interacts with that normal table visual in my power bi report now to check this i'm going to go here to file and then i'm going to click on save as and we're going to save it to the cloud and of course we can give it a name so we can call it right comment
and that app that we're going to create we can also say who we want to share it with all right so now let's go back to the power bi report so now we just want to check if that table visual interacts with the power app visual now the next thing that i want to do is to show a form on that powerapp visual that lets me change the selected entry here in the table now here in powerapps i'm going to delete that gallery that was just showing all of the client information so that's not what
i want so i'm going to delete that and what i do want is a form that lets me change the selected entry so i go to insert and then we can go here to forms and click on edit so now we have to add a data source to the form and so the data source we want to write back to so we have to click down connect to data add data and then here we can go to connect this and then choose the type of data source that you want to write back to now in
my case i'm going to first use a normal excel file that i saved on onedrive and you see we have there onedrive for business let's choose that one and then you can select for example your excel file choose the table that you want to use as the source and click on connect so now you added it as a data source for this app and if you want to use it then for this form you still have to go here on the right hand side data source and choose it from there okay so now we can
add the fields that we want to use for this form so let's click here on edit fields then add field and now you can click on all of the fields that you want to use then we can collapse them and put them in the order we like to have them okay so first the client id client name subscription status and cancellation reason now let's resize this form and let's create a little bit of space there at the top here i want to have a header so let's go to insert i want to add a label
and let's change the text of this label let's say add a comment just like this also here on the properties on the right hand side we can change the color for example of the background we can have let's say blue and let's say we want to have a white font then we can also put it in the middle and resize it over here at the top and maybe we want to have it in bold so now we have that the next thing that we want to do is to make sure that the form shows the
information for the selected item the selected item in the other table visual in power bi now how can we do that well this doesn't happen automatically we have to set it up and the way to do it is by going here first of all to the tree view and then select the form then here we have a drop down from which we can make our selection of what we want to change now here we want to go to item because we want to change the text these items show and we can do that with a
formula so now we can use a function called lookup so over here we have the lookup function and the source in which we want to look up a value is going to be that client table so let's refer to them client and now the condition that finds the row the value that we want to return so this is going to be based on the client id so i take the client id from that client table and this needs to be equal to well the selected value in the table visual inside of power bi but how
can we know what that is so now we can use another function which is called first which returns the first row of the source and now we need to refer back to that power bi integration that data that we added to the power app visual so we can just type in power bi integration so integration dot data and then we can close that bracket add another dot and then from that role we want to have well we want to have the client id over there it can be that still gives you a warning like in
my case well i have an issue with my data types probably i have to fix that or i can just go over here and say that the first one needs to be an integer that will also fix it so now we have a form that will show this selected row in the table visual inside of power bi now let's see if that works so i'm going to go again to file i'm going to save it and important you have to click again on publish so it usually takes a few seconds for it updates just add
another page go back and forth and then it reloads that powerapp visual and after a few seconds you see it's there all right now at the moment it shows client id well 1001 with all of the information well i have nothing selected but i said give me the first one all right so that makes sense so if i select the second one now you see oh it shows me the information of the second client or the third one so that is working however what still is missing is that when i would change a value for
example i would put in a cancellation reason well how do i click on ok there's no button yet i forgot about the button so let's go back again and you can simply add a button here on the insert then add a button and let's put it here at the bottom and then also let's change the color to the same color blue so that nicely matches maybe you also want to have a different text then let's go here for okay and when you now press that button nothing happens okay so here on select equals false doesn't
do anything so what do we want to do we want to submit the form so let's click on here the function submit form which form just look over here how it's called i didn't rename it you probably should but i didn't and i can refer back to form one another thing that i would do is to set up what needs to happen when the information successfully wrote back to the data source now just make sure that you select the form then you go to the drop down and we can say what needs to happen on
success so at the moment it's set to false however if you want to reflect the changes straight away then one thing that needs to happen is to refresh the visuals and you can do that by saying power bi integration dot refresh close the brackets now this is not refreshing the data source which we will talk about in a second this just refreshes the power bi visuals okay so that's one another thing that i would add over here is a message to the user that it was successful so we can say notify and then we can
say success and then we can say what kind of notification type we want so notification type success and then over here the speed and close the brackets so with that semicolon over there we can basically add multiple commands that need to happen when the form successfully submits so that looks good now let's save it again and go back to power bi so here we are back in power bi i refreshed the visual so here we have the button and now i'm going to select let's say the second client you see nicely updates then we fill
in the cancellation reason so this is going to be test 2. i'm going to click here on ok i see a nice message pops up success i can click it away and now i look at my table visual nothing happened why not now it's important what you're using as a source now i wrote back to that excel file that's on the onedrive and we are currently in import mode not direct query so the change doesn't automatically get reflected i still need to refresh that table which is not ideal so later on i will switch to
a source that can use direct query instead of an excel file on a onedrive all right however now let's see what happens when i now click on refresh data boom there you go we have das 2 over there in the cancellation reason column it works however you probably don't want to click on refresh you want to see it straight away now what are your options when you do use excel on a onedrive as your source or a sharepoint list something that cannot use direct query well then we can trigger the refresh now to trigger the
refresh we can do it in two ways now let's go back to powerapps now the first option is to select the button and then over here we can go to action and then we can go to power automate here we can click on create a new flow we will go now to power automate then here on the templates we can go for powerapps buttons we can also go here to create and then here an instant cloudflow and then we want to use powerapps and click on create now here we trigger the action from powerapps and
what needs to happen well this is going to be the next step we want to refresh a data set inside of power bi so over here we can go for power bi and then refresh now here we have to choose the workspace and the data set however i didn't publish my report yet so let me do that quickly i go back to power bi then over here let's click on publish then choose the workspace you want to publish it too and click on select so now that's published i go to the workspace where i published
it to then we can also select the data set so over here let's choose the data set click on save so now i'm back in powerapp i have the button selected went here to action and here we have the available flows now the flow that we just created should show up and the one that i previously set up now i'm going to choose over here this one then you just have to close that bracket so that runs and that's it now the advantage of this option is that you can make further adjustments to that flow
so if you want other stuff to happen automatically that's possible however you don't necessarily need to use it there's also an alternative where you don't need flow you can just do it from within power apps which you can do as follows first of all you have to go here to your data sources then here we can add a data source let's look for power bi and then connect to your environment and then i go back to the tree view and select my form and then here we want to go again on success because there we
can say do not only refresh the visuals the power bi integrations dot refresh but here we also want to refresh a certain data set that we published to power bi service so let's set this up i'm going to go to the formula bar and here we want to say power bi dot refresh so refresh data set there it is now here we have to give the group id and the dataset id in between quotes like this now if you don't know where to find them just go to power bi service go to the workspace then
select the data set then in the url the part that's in between the forward slashes that follows after groups that's the group id and then we have the data set id and the part of the data sets all right just copy that over and then again and we can save it and go back to power bi and you're probably thinking well it doesn't really make much sense to run a refresh every time you make a change and that's true because you are limited to only eight refreshes if you have a pro license so if you
want to make more than eight changes well that's not possible so therefore it's probably much better if you use a source that can be used with direct query now what could that source then be wrong for example sql server or dataverse now how would the experience then be if we have source data that can be used with direct query well here i have an example so if i select let's say the second client then over here we can we have a form i put in the cancellation reason so over here that's do click on okay
says it's successful and also automatically refreshes that table visual that i have over there so you see it's instantly updating the visual and the source data and you're probably thinking would the setup be much different or much more complex no not at all how you follow the same steps it's just you choose a different data source where we chose onedrive before so and of course here the tables themselves need to be in storage mode directory now another thing that should be addressed is where do we put that form are you going to just have it
on your page always visible well that's an option but maybe you don't want that well what other options are there well the first thing that i was thinking of is maybe a tooltip because then it doesn't take any space and you just hover over the line item that you want to change and go to the tooltip change it there so you could create a custom tooltip with that power app form and then use it over here for your table now the whole setup i'm not going to cover in this video that's not the main focus
however once it's set up you just go to the line item that you want to change and then you go to the tooltip but the tooltip also moves so it's not really a good solution that would be really nice if power bi just adds this functionality that you can go to the tooltip and make changes would be very nice however there's a little bug if you're really quick then sometimes you actually do manage so it probably takes you a little bit but you see now it worked for a bit and then it disappeared however let
me try again and again and again you see after a minute or so then you probably manage one time to actually go to the tooltip and make any changes all right so that's kind of nice to know but it's not really functional all right then another option that you have is to work with drill through so you could also create a separate page put your form on that page then set up the drill through on let's say the client id and then you go to the line item for the visual that you want to change
drill through drill through page so you go to that form and then you make your changes and go back so that would be an actual solution that could work the solution that i like the most is to overlap the table with the powerapp visual and then use bookmarks to either show one or the other which then can link to a button right next to it so that you can click on it and it shows the form and if you click on it again it shows the table so this is how you can use powerapps to
write back data to your data source now i hope this is helpful if you have any questions just post them in the comment section below i want to thank you for watching and if you got some value out of this video then consider subscribing i hope to see you in the next video
