Please log-in to view this video. Sign up for FREE ACCESS HERE
Learn how to set up a Power BI Data Source with Data Refresh from John White.
In this video by John you will learn how to:
(1) Build a Power BI Report from file based data.
(2) Installing and registering the Personal Data Gateway for Power BI.
(3) Scheduling a data source for refresh in Power BI.
Power BI Data Source with Data Refresh from John White.
Here’s our scenario I have a mileage tracker on my tracker on my iPhone that records all of my movements for mileage purposes for business it allows me to download reports on a periodic basis and they wind up as CSV files in a folder on my hard drive I want to take that data and surface it in Power BI because it’s whole lot easier to consume it I want to keep it up to date whenever I add the file I want the data in Power BI to reflect that so let’s walk through the process of making all of that happen the first thing we need to do is obviously connect the Power BI and get Power BIdes top so I’m just going to quickly go into app Power BI com I’m going to signing as myself and I have 2-stepverification turned on of course so if I’m going to have to enter in a code and I’m signing it we load up the power band I’m taking my default dashboard which is a Google Analytics view of my blog and I’m going to come up here first thing I want to do if I don’t already have it is get a Power BI desktop so I’m just going to click on that link and it’s going to download into my downloads folder now I have already done that so I’m just going to cancel this download and I’m going to open up my downloads folder and launch the power by desk that’s opening up off screen so I’m just going to bring it over here onto the screen I’ve hit next it’s a next ok cancel install I’m going to go ahead and accept the terms of the license agreement of course without reading them does anyone ever read these things next and install I’m going to let that go ahead and install I’m going to pause the recording here we’ll come back when it’s finished and now the installation is finished I’m just going go ahead and launch Power BI desktop by selecting finish and up it comes in behind my browser but up it comes okays right off the bat we’re presented with the option of going and getting data or just closing this window off and working from scratch we’re going to go-ahead and get some data and one of the things we want to do is load from folder so you can see the power by can load from excel XML text etc. etc. but it can also load data straight from folder we’re going to go ahead and do that I’m just going to connect it’s going to ask me to select a folder so I’m going to go in here and I’m going to open up my personal OneDrive folder that’s where I store it in a folder called data and mileage I’m going to click OK and ok and it’s going to go and scan that folder and it sees that there’s two files in there right now I’m going to want to do more than just load the metadata for the file so I’m going to click on edit and that’s going to take us into our power query editor drag it over to the main screen here and from here we want to drill into the content of the file we can do that here if you look at the content column that Power BI has used when it pulled in the file information you can see that’s actually the file itself so I want to drill down into it I’m going to pick that and you can see we actually get the data itself so you can see this is showing on an for both files in that file folders iterating through all of the files all of the mile now one of the things right off the bat you can see that the first row is actually the column titles that’s easy enough to setup we can simply go in here and we can say used for first rows header so I we clicked on the little table icon to the left of all of the columns and I’m just going to say use first row as headers okay now another thing for this visualization I’m only interested in my business data not my personal data and personal data is showing up here as other so I’m going to take the purpose column and I’m just going to deselect everything and I’m going to select business and click OK and now you can see all of the trips that we ‘retaking based on business now that that’s been selected you can see that all of the values your business I don’t need this column anymore so I’m just going To go ahead and get rid of it remove and I’m going to get rid of vehicle I’m only dealing with one vehicle here I’m going to hit remove and you can see again all of the journeys now the tool itself will calculate any expenses I don’t use this for any expenses so I don’t need that column either so I’m going to go ahead and remove that however I do want a column that’s based on the on the charge rate for per kilometre however if we look at the distance column if we select it we can see up at the top that the data types type text we can’t perform mathematical operations on a text field but these are in fact all numbers all we need to do is manually change the type we’re going to change it to a decimal number and now we’re going to go up here to add column we’re going to add accustom column we’re going to call it charge and we’re going to use the distance as a base and we’re simply going to multiply that by zero point four six forty six cents per kilometre and bang we get it we get a number out of that but actually it only looks like number field in fact if you look up at the top the data type is set to any basically meaning we’re not specifying the data type at all we should come down here and pick decimal number so that when we load it into the model the model editor doesn’t have to guess what datatype we’re dealing with likewise if we look over here we have a date column let’s just select that once we select it we can see it’s flag does data type text well in fact it’s a date we should flag it as such and I do that you can see that it shifts to the right hand side but now when we load into the data model it’s going to know what data type we were working with and to load it into the data model we go ahead here and we’ve got to close and apply and the data has been loaded into the model we’ll see this if we click on the model editor you can see all of the data we were working with previously including that charge column that was calculated now one thing about the charge columnist’s a crank field we couldn’t control the formatting of that right within power query but we can with a model editor we will always want this to show up as a as a currency field so we’re just going to click on that currency icon and we’re going to pick the dollar symbol and there you have it we have exactly what our charge rates were for all of these trips in the MA data models from here we could go ahead and establish some any relationships we have with other data we don’t have any right now we just want a quick visualization of what our data looks like so I’m going to click back up here on report and we build a quick visualization I might want to see how much how much I’m driving for business so we’ll quickly pull in a column chart we’ll just use as tacked column chart click here and we’re going to look in our model for what we want to show and it’s going Tobe a very simple chart we’re going to use date along the x-axis and we’re going to use just a distance in kilometres now Power BI is smart about date if you look at here under our axis section you can see it’s showing a full hierarchy date year quarter month and day I’m going to use a little drop down here and say maybe I don’t want to show the hierarchy maybe I want to show the date well that’s not exactly what I want either in fact what I want I don’t want to show the year let’s go back to the date hierarchy I really just want to show a month and day so what I’m going to do is this deselect date or sorry a year in Quarter and this is more what I’m after so a month by month comparison of mileage patterns here and I’m going to come down here just below and I’m going to put in a simple table because I actually want to see the detail of the data I’m working with and I’m just going to drag this over here and I want to show the date I want to show the deltas should have had that visualization selected when I selected those so I want to show the I want to use the date I want to show the distance want to show the end election is an end location I want to show any notes that may be there and I want to show the charge amount so we can see bang we’ve got a really quick report here as of course it’s power by so everything is cross filterable anything we select you can see the detail table like is reflected accordingly and we’re ready to save it up to power to do that I’m just going to go up to the ribbon I’m going to go ahead and select publish yes I want to save my changes I’m going to have to change save that off into a folder I’m going to put it that in that same mileage folder that I was using before so where we have it OneDrive personal data mileage and mileage report go ahead and go ahead and hit save and I need to sign into my Power BI account if I’m not already apparently I’m not so I’ll go ahead and do that and once I’ve signed in the report will publish and there we have it we have success and an off one of the options is to go ahead and open it in Power BI I’m going to do that and you can see that we’re loading data and it takes us directly to our report in power and you can see in the browser is working exactly as we would expect it to now we also want to keep this data setup to date that is to say we want to update the dashboard if we add a new file into the folder the way we would normally this to schedule Reis a refresh for the data source the way we way you can find that by going into our power been scrolling down finding our data set we’re down here at mileage report hitting the ellipsis and hitting schedule or refresh now you can see here that the system is intelligent enough total us that we’re not going to be able to refresh this data source without having a personal gateway installed you can install that there’s a couple of ways we can get at it we can come out up here and go into Power BI gateways but there’s a link right here for install now and that’s going to take us to a web or sorry to the to the download site for the Gateway I’ve already downloaded the Gateway so I’m just going to hit cancel and I’m going to go into the download folder and launch it and launched another screen I’m just going to drag it over here so we can see and it’s again going through a fairly straightforward installation process and next cancel the sort of thing I paused recording there so you didn’t have to wait two minutes for the installation but at this point in time again it’s the next cancel so we hit next accept the terms of course next where dowel want to install it go ahead and install it there and we’re done that’s really all there is to it we’ve got it installed now we need to launch it and we’re going to configure it so I click on the launch button there and you can see we need tossing in to power by comma I’m just going to click there enter my credentials and again enter in a verification code and okay and away we go so right off the bat we can now see tithe service can see that we’ve got our Power BI gateway our personal gateway installed but it still can’t do a refresh because it doesn’t know who I am I just hit refresh and we’re going to going here now and edit my credentials it’s basically saying here’s the path we know you need to get to how are you going to attend take eight how are we going to know who to connect as and I just need to now sign in I’m going to enter in credentials they’re going to be stored securely within the Power BI service now it used single sign it knows who I am so now we’re ready to go we can go ahead and schedule our Refresh I’m going today keep the data up-to-date yes we’re going to refresh it daily what’s our time zone what we can add specific time I’m going to say let’s refresh this thing at 10:30 a.m. apply and away we go and I’ve also got this box selected here Saints send refreshed failure notification email so if I can’t do a refresh for any reason I’m going to receive an email so next up we obviously want to test it to do that I have got the obvious outlier in a file that I’m going to take from here and just move it up here I’m going to put it into the same folder with my other file so thesis to simulate adding another log file just a single log file in in this case but it’s got a fairly obvious outlier we’ve got a trip from Mars to Venus so I’m just going to drag that out of the way and we’re going to go from here we’re going to go into our power by menu I’m going to scroll down data sources and I’m well actually first I’m going To go back up to our original mileage report so here is mileage report so we can see what it is right now I’m going to scroll down here and go into mileage where hit this little ellipsis beside mileage report and just click refresh now and you’ll see in the upper right hand corner it says preparing for refresh fora second and then while it’s doing there fresh you can see if you look at the mileage report entry in the menu you see this little spinning icon to the left of it and that’s going to go on for just a moment that looks like it’s done now we haven’t seen any change in the report because it doesn’t automatically update immediately and what if we were out fora while its but we’re working on data but all we have to do is hit this refresh button and we should see an immediate significant change to our dataset and we do we see December was a very big month for travel about 15 million kilometres and here we have the entry that shows up at the right date the same date that we put in in the workbook but with the new data so you can see that the refresh is working in this case it wasn’t scheduled we did manually but it works either way through the gateway so we’ve gone we’ve gone from nothing just having a set of common delimited files we’ve created a data model we’ve uploaded it into Power BI we’re able to youLog In