Modify individual cells without write permissions in Power BI | Write back in Power BI

so hallo everybody in today's video I'm going to show you how you can write back to data source that does not allow it you've seen Harvey I and perhaps all of that in just a second okay so I'm going to show you an example here probably not the best but you would get the idea anyhow so I have here a table it's a supplier allocation product ID purchase value and discount and imagine that you would like to be able to input new discount in here like you would do in Excel you know you click on a cell and to get a new value now this data source it's actually data embedded in power bi but it could be a web source that you don't have access to it could be that you imported from Excel files you clean the data and that data is clean and you don't have access to it anywhere and you would like to actually be able to write back to change values into in power bi so how do we do that this is what we're going to do we are going to go to SharePoint if you have office 365 you have serpents you will be able to do this in SharePoint we're going to create a site and then we're going to create a new list we're going to call it discount create it and here we're going to add a new column where we will put discounts so it's going to be a single line of text no no no no no it's going to actually be a number number discount and then maybe we should call it up dated so we can then differentiate any Barbie I updated discount and then we're going to have these as a number with the two decimals and the shape and we're going to use the title as the ID for the line so we're going to go year list settings is the easiest way then when we create a powerup to manage these so this is going to be our row ID okay so we go back to these consonants and now we have a list that contains a row ID so an identifier for each row not even under the discount we're going to go in here and click create an app and it's going to do discount who's going to do all the work for us it's just amazing so I'll show you this is actually creating the app we are looking mr. cool takes around 30 seconds it goes very very quickly we're going to modify the up a little bit just a bit exactly what we want but it is more or less ready come on baby yes so here we have that let me show you if you go here to play you will be able to interact with the app if we go to plus you'll see that I have here an attachment parts that we don't want it is here in the Edit screen so we're going to get rid of that we don't want that when it comes to the detail screen that is fine and then we comes to the browser screen that is also fine let's give it a go let's go to sort of it's good to test your 0.4 and there we have it good as you can see it has a newline we don't want that so we're going to click on the actual box and then we say we want to have only title and subtitle I don't want to have my name in there and what we want to do is put some labels here so you have to select the item that you want and then click on insert label and then I don't know where my name is yousef all the time row ID and then we are going to make it a little smaller a salut it's heavy and to test that this works as expected I always like this a create another row zero point six or five that's fine seven and as you can see the label follows along which is exactly what we want and we are going to have also one for the discount so label discount again we want this a little bit smaller that's a little bit too big make this thing smaller to put it in place somewhere obviously you can do this a lot nicer that I'm going to the idea but this is something that you can I'm sure and when it's yourself so let's count there put this up here make this a little bit bigger looking good so this is our app we were going to do is we're going to save it I'm publish it so we can use it in power bi so publish publish this version okay so now we have our app done let's go back to power bi okay so now we go in here go to the power ups icon and remember you have to throw in a row or a field in here in order to be able to have a responsive a bit annoying actually because I always forget to tap and then here's our discount app I'll skip and here we have the app right now we want to have the information from this app back into power bi so we're going to go here go to new source blonde no blank query sorry the lead we're going to new source we're going to more and then we're going to choose the SharePoint list stinking okay I think it is online services SharePoint list and then we're going to copy the URL paste it in there and we would remove up to the site name okay and then you go to you have to sign in connect and this will get us the list of new discounsel we are inputting manually nothing we are here so come on baby here we're going to get all the stuff that is in SharePoint we want to have our discount I think it's this one discount okay okay I pick the right one otherwise we're not going to get the information back okay so now we are going to find our row ID and our discount remove everything else give it a proper name row ID and then this is text and this is a decimal number great let's load this into power bi so now every time we write something into perhaps he is going to update the SharePoint list and then Barbara is going to read from the SharePoint list and get that data back into power bi it is a one of the workarounds to do these so here with our discounts table perfect now let's look at the relationships and see how okay didn't do anything so yesterday I have shown you in - - - videos how to create a row ID for a less for a table so I'm going to link to them down below so you can see them and they will pop up here somewhere so I have already a row ID that I'm going to put in my table and this is the first one so again I have two videos on the heart of this exactly it says don't worry you will be able to follow along so you can see here that now every row is identified with a number which is the numbers that we have here so row 12 is row 12 so now what we're going to do is put row ID with variety do a relationship between those tables and then we're going to put our update discount in two and you can see is 12 and 13 that we have changed and those are the ones that you would have good now we have just the last part we want to have a new update column or new discount column that will do like this if there is no new discount pick the over but if there is a new discount pick the new one how do we do that very very you see we go to create a measure come on baby new this out this count and then we do we're going to create a variable for selected or for up oops because we're going to use this twice updated discount which is going to be the selected value or updated this cut return and then we are going to do if is blank so updated so if our updated discount is blank then put the value for the whole discount which is the supply discount otherwise give us the new updated discount okay so we have the new measure new discount put there and as you can see is working beautifully here is zero 20% is being overwritten by zero four you can actually create a parameter here that you can change the discount for all tables and then you can individually change every this kinda super cool let's see if the use of works let's try it so let's say that we want to Chandlers like always zero 70 percent discount is a little bit too high so we go in here at it we say I want to have 0.3 sounds better and then I need to just update this the discount otherwise you have did everything obviously but you know why do that you can see that it changed since your point three and then you can add obviously a new one you can say okay I wanna have row ID 24 I wanna have a discount of 0.7 updated goes up to share a point changes it there Harvey I pulls it back from SharePoint bring us in here and mash it up on the average how cool is this

Channel: Curbal

Views: 57,044

Keywords: Power bi, powerbi, Curbal, power bi desktop, power bi tutorial, power bi training, power bi for beginners, ruth pozuelo, write back in power bi, write-back in power bi, writeback in power bi, write back in power bi without direct query, write back power bi and power apps, curbal power bi, power bi dashboard, power query

Id: -dRDKbm-v6Y

Length: 12min 27sec (747 seconds)

Published: Wed Apr 29 2020