Python Refresh Power BI Desktop File

hey what up everybody i am lou and today we're going to talk about how to have python refresh a power bi desktop file so in power bi there's a refresh button on your on a power bi file and ultimately we're going to use python to actually make that action right to kind of click that refresh button to to refresh it so i'm gonna i'm gonna kind of show you the package that we're gonna use kind of give you a high level overview of what um what it's doing not gonna go too details but you know ultimately it's referencing another package and i'll kind of go you know a brief description over that package and then we'll go and test it out ourselves i have a power bi file that we can test out so we'll play around with it test it out and kind of see it in action so before we get started let me show you [Music] so this this pbix refresher this is a package that we're going to be installing um to get you know python to refresh your power bi file now it's actually very easy it really is but i'm going to touch on some basics on what it's doing so at least you have a somewhat basic understanding but let's go ahead and get started and we'll we'll test it out right quick all right so i have um an empty project so as you could tell i do have a power bi file in here it's called container report dot pbi x so what i'm going to do here is let me get my um my terminal all right so i'm already in the file it's called python refresh power bi desktop first thing i'm going to end up doing here is uh create a virtual environment so let's get that set up um so it would be python oops um what the hell am i doing um i'm gonna call it env let's call it that and you could tell create a univ file ultimately again if you're not familiar with virtual environments get familiar with it man this is like a must known like as you start the level pin and especially when you start pushing stuff out to production and things of that nature you got to have a virtual environment man you have to and the reason why there will be times where maybe you're working there's another project that you push out to the same server that is using different packages and you may end up realizing that based on what you build it may only work with this older version of the package but this other app that you have on there works with the works with a newer version for whatever reason the last thing you want to do is have the main python interpreter have all of your packages that you're installing then have that shared that one interpreter shared through all your projects because then you're gonna you could have easily you know pac package conflicts where one doesn't work because it needs a newer version but if you upgrade to the new version it breaks the other one to avoid that you know that's where virtual environments come in again that's a whole different video but if you're not familiar get familiar with it so let's go ahead and continue on all right so now that i got that started i got that created let's let me go ahead and connect to it um activate all right so i got it activated um next i'm going to install that pbix refresher package so that's pretty much what i'm doing here let's give it a second to finish come on man all right cool so now that's done so here's the thing what i just did right now i installed the package pbi x refresher and technically that's it man we're done right just kind of show you we are done so if i want to use it to refresh a power bi report which let's go ahead and do i'm going to call p b pbix refresher which again it's part of my virtual environment that i created uh next i am going to call this file this power bi file that i have so let's go ahead and container report.ppb pbix then i'm going to specify um a no publish setting and i think that's it so let's go ahead and try it out okay so one thing to keep in mind it is think of it as to some degree it's it's not moving your mouse to refresh like a specific item but it is it is using it to some like underneath this i don't know how to explain it properly but you're not going to see your mouse move and click on a specific tab on top or anything but in reality it is using someone like that functionality where um probably the best way to say it is if you were to have this like the way i'm running this script right now it opened up power bi it should be refreshing any second now hopefully we don't have errors but ultimately it should be refreshing i don't see nothing refreshing so it is taking a second but let's give it a second nevertheless it should be refreshing any second now and but if i if i'm moving my mouse and doing something else then it doesn't work like it's not going to work because it needs the mouse or nothing to actually be happening at the moment um as you can tell my my my pointer disappeared right i don't see my pointer and that's probably because it got redirected up to the refresh section and it should be refreshing again i'm still waiting for the refresh oh there it goes it actually did get moved never mind so it is controlling the pointer it got moved to the home tab [Music] um so let's see what's happening here guys i think i may know what happened oh there it goes never mind never mind it just took a while boom you refreshed and we could see and again i'm not moving the mouse i got my my hands over here [Music] and so never mind you know what i was wrong it is moving the pointer i don't know if you notice the pointer got moved and where that that's where the refresh section was at so it is controlling the mouse and it's actually clicking on the refresh button then it clicks on the save button and we look at the terminal the reason why it took a while there is like a wait period where it's waiting a few seconds but see it identified power bi window it refreshed because it clicked on the refresh button waiting for refresh ending um and then there's a saving then there the exiting which ultimately means it's closing out so ultimately what it did it opened up power bi it clicked on the refresh button it then hit the save button and then it closed out it exit out and then you could tell the application is done so you may wonder well why would i want this i mean can i just go into the you know let's just let me go to it right can i just go into the folder and say hey let me go ahead and open up this power bi report and as it opens up hey let me go ahead and refresh all right because you can right kind of what i'm doing let it refresh okay once it's done refreshing let me go ahead and save and then let me go ahead and close you could do that if you're dealing with a power bi report that you only use uh for example like you're not nobody else uses a power bi report by all means you could do that where this comes in handy is more of a scenario where um let's say if you're sharing a power bi file like i want to give you a good example let's say the power bi file lives on a server that nobody has access to because you know only admin or you know certain people have access to that server because that server let's say he is hosting you know uh one of the main databases and so on but let's say you have power bi living on it as well like desktop and you got the power bi file on that server and on top of that nobody could connect to that server you can't connect to it externally uh and again this is the real scenario with one of my clients um i'm not going to go too detail but ultimately anybody who's familiar with a quickbooks enterprise um these applications are installed on a let's say a specific server and you really cannot connect to the database the quickbooks enterprise database like from another server another computer like the way you do traditionally to data you can't do it you could connect to the database when you're part of that same computer that you're logged into the same computer but besides that you cannot just connect to it like any other database so but in in in my example for the scenario that i'm talking about we had a process where power bi would open up again kind of what we just did right now it'll refresh when you refresh it's connecting to the quickbooks enterprise local database that you know only people that are logged in have access to and then of course you refresh the power bi file the power bi file happens to live on a onedrive account um and also it's we were able to drop that into a sharepoint so there's a few steps that when i'm doing it ends up being part of anybody again if you're familiar with onedrive onedrive ultimately under the hood is pretty much a sharepoint site to some degree either way as it refreshes it puts a fresh copy up to sharepoint we were able to bring that file from sharepoint over to power bi online and of course now peop other people have access to that file and they're able to see the fret the latest version of that data now there's better solutions to that like if the client decided to purchase the power bi um not the pro license but collect the enterprise license with like five thousand a month or whatever but but in my case the client that was a lot of money kind of didn't want to spend that much money so we found a workaround and that's kind of what we did and that's just an example right but either way that was a scenario that i came across and that's what i have to do so what i'm showing you what i'm doing right now this is a real scenario of what i had to do one of my projects and this then i got to work and it worked for me and that's kind of what we end up doing so now let's kind of talk a little bit more detail what it's doing now you kind of you kind of saw right now it's i don't have no python file there is no python file literally well what i'm doing is i'm installing this package this package has if i go back uh exe file by me executing the exe file it is executing some python code behind the scenes um and then i'm passing in my file name that's what this is now you can tell my file name is part of the same folder but i just say that it wasn't right then you just specified the full direct pass and let's go ahead and do that and if i were to do that it would look something like like this right your c pass i don't know whatever it is then of course you have your file name right so that's kind of what it will be the no publish and let's take a look at the um uh let's go ahead and switch over so if we take a look at some of the arguments right so we here we have power bi refresher which is a power bi refresher.exe the workbook as you could tell you know i had the full pass the file name and then you have some arguments that you could pass in as well like workspace if you're trying to publish online here's the issue with the workspace i tried it out it did not work for me so there must be something there's some conflict going on there having like try to deep dive into it to figure out why causing that issues but it is causing issues uh refresh timeout um [Music] so this is towards the end when it's about to be done i have not tried that out the two that i have tried is these uh no publish which means you do not want to publish it to a power bi online uh portal and then of course this other one is init weight so this is very important on this one i may explain to you why just like for me in the example that i had with my clients they had a remote server that they were they were paying for from you know whatever vendor you know it vendors they were using so they gave them a remote server and they had very bare minimum resources i believe it had i can't remember honestly like two virtual cpus or something and like four gigs of ram it wasn't much right it was pretty slim and what happens is power bi is a little bit more on the heavy side of resource so every time the pros like we run the like just opening up a power bi file that like it would literally take a minute and a half it seems like for two minutes to open up now on my computer it opens up pretty fairly quickly in seconds for the power that they had on the server it was taking over two minutes to open up the freaking power bi file which is crazy now because of that that's where this init weight comes in which means by default it takes 15 seconds then the power bi um it gives it 50 seconds for power bi to open up and then as it opens up it starts going to the actions of refresh and do all that stuff but if you need more time because again it's taking longer you could change that init weight to let's say 300 seconds i mean or something like that right so it was 60 seconds in a minute so 120 seconds would be two minutes you know 180 seconds will be three minutes and so on and so on so you can kind of specify if you or if if you are going to have some kind of process to refresh your file maybe you create some sort of virtual machine and then you're going to have basic you know very small minimum resources on that machine that's fine but then that's where you could extend it out where it gives enough time for the file to open up properly and you know get everything set up and then you kind of go to the actions so that's a big big um thing need to know when depending on your your hardware right from a performance so again the usage is very straightforward very easy now let's kind of dig into a little bit more um let's see so if we if we kind of read on this i think this touches on it i don't know if it does if if it touches on it or not i am familiar with it there it goes it does touch on it py win auto so ultimately this is kind of what it's using so let's take a look at that that package if we take a look at that you know what is py auto so ultimately as you could tell it's a set of python modules to automate microsoft windows gui's pretty much high level what this is it allows you to interact with microsoft applications to control the start edit different things of that nature if you wanted to it gives you that abilities to do that and here's kind of a basic example down here we take a look at it right you know where you're ultimately starting the notebook pad as you can tell very standard right on every windows then you're pretty much you know um you're typing in specific you know keys trying to find a better example like here goes the app open open.click the one example that i saw that was pretty good is see like menu selection you know you're selected from a specific menu and then again let's let me go ahead and open up notepad right we can take a look at it so just kind of compare to what we're seeing so we have format then we have font right so exactly what we see here this is pretty much the command that is being told on where to kind of direct it to you and then you're saying you know okay.click which in this case is font we're specifying font okay click type deal so a lot of the stuff that you're able to do here that's where this package comes in right here so if you want to get into automating certain steps this is a good one the one that i have actually used heavily in the past is this guy pywin32 so this one you're accessing the the windows um com api and with that you're able to kind of interact with excel word all these other applications as well to not just with the you with the ui but actually when it comes to other functionalities again uh they're very interesting to use if you're if you're trying to um interact with certain excel files there's other packages that allow you to do this but what makes this one very uh unique in my mind compared to like open py excel or something like that is an open py excel you're actually creating an um xml file pretty much like an excel file from scratch it's what you're doing something like this the py-132 you're actually interacting with real excel um uh you're you're actually interacting with um you're actually interacting with a microsoft excel file that have already been generated generated and you're you're able to access certain um certain cells and certain functionality was in there without having to copy that data put into memory because that's kind of what open py does as well a lot of the activity you're pretty much reading it you kind of bring it into memory and you're interacting with it you kind of save it back out where with pyo 132 you're actually just opening up the original file then you're able to use the calm comm api to kind of actually integrate and cross current commands to let's say write data to cells or do some other functionalities so it's it's a it's a pretty cool it's been around for for some years but either way the main purpose i was trying to tell you here that's what the the pbix refresher it's using that package as well and that package ultimately what it is what is used for is to interact with windowed applications you know guise right you know access the any drop downs any other you know any um uh any of the menus that kind of stuff you're able to interact with which is why this works which is why it's able to control the mouse point it to the right section to do what it needs to do so overall it's very simple there was nothing complex about this what we did um it's literally a matter of bringing in and using the pbi x refresher package um and then you're good to go man i mean to you to refresh now a heavy need for it that just depends on what you're doing but for me i would say if you're in a position where you have a power bi file you're trying to share with other people um that is where it comes a little more handy especially they don't have access to open the file and let's say you're pushing this file out to a sharepoint site right you know it lived in a sharepoint site and that's kind of where it comes handy because now people can just open it up like download it from sharepoint if they want and run it and it doesn't matter if they're connecting to the internet or not or after they download it they're still connected to the network or access to the database or even connected online once they download it they're offline they could see all the latest data that had been pushed out there because you can have a job that's automated let's say they run every 5 a.m to refresh that data and ultimately the copy is being saved to sharepoint for anybody else to download in the future and they will get the latest data as well so that's kind of where it becomes a little bit more handy um man hopefully this helps out man hopefully this is beneficial give you some insight to it if you ever get stuck when it comes to this again give me a thumbs up follow me i'll be making some more content again i'm i'm you know still grinding this out and you know hopefully i can help you all out and if you have any ideas of certain videos you want me to to do let me know i my programming i do uh i don't just program and python javascript you know some c plus plus things of that nature that's it my bad c sharp i don't know why i said c plus plus but i was just thinking of it um because i was actually thinking about learning it nevertheless c sharp um dart just kind of name a few panera but my main language is python man i enjoy it i love it and that's kind of what my when i'm going to make my youtube videos mainly focus on the python you know i'm going to be narrowed down on python that's where i've been using python for some years that's where my strengths is at so anything that you have python related let me know um email me send me a comment whatever maybe and yeah i may make a video on it um so man thanks for watching again follow hit the like button and peace

Channel: I am Lu

Views: 6,738

Keywords:

Id: wtXXpicCUlY

Length: 24min 33sec (1473 seconds)

Published: Sun Jun 20 2021