2: Importing and transforming data using Power Query Editor
2.6 Explore data source settings
2: Importing and transforming data using Power Query Editor
2.6 Explore data source settings - Video Tutorials & Practice Problems
Video duration:
6m
Play a video:
<v ->In this sub lesson</v> we're going to explore data source settings and do a demonstration around how to change our source settings. So let's go ahead and get ourselves into the power BI desktop that supports this sub lesson. Okay. So I've got the folder open, that contains the files from GitHub. And what I'm gonna do is open up the one here called SalesDataMart SL02_06 Start. So let's go ahead and open that power BI Desktop file open. (mouse clicks) Okay, so now that the file is open let's just show you a couple different ways that you can get in to modify your data source settings. So first from the power BI desktop itself what we'll do is we're gonna click on the file tab. And if I go down a little further here I can see options and settings and then I'll see a option over here for data source settings. And that's gonna go ahead and open up a dialog box that shows us all of our current data source settings. I'm gonna go ahead and close this out because I actually want to show you how to do this through the Power Query Editor. So what I'll do is once again home tab, queries group, transform data command, click on that and that gets us in to our Power Query Editor. And we can see here that we also have the same ability off of the home tab under the data sources group, data source settings. So I'm just gonna go ahead and click on that and get the dialog box open. Okay. So on the data source settings dialog box here we can see that there are two radio boxes one that says data source settings in the current file which we can see down below. And then a there'll be this other radio box here called global permissions which we'll talk about momentarily. So let's focus in on this data sources in the current file and what this is doing. So if we look down below, we can see the seven different data sources that we connected into. So the Excel spreadsheet for order date, product level text, product sales territory going all the way down to the web and plus our folder connection here. So each of these has a location. Now, if you're able to grab all the files off of GitHub and you've been following along and you did what we asked by putting them on the C drive and into this folder here, then all of us are doing this course would have a common mapping. But not everybody is gonna be able to put their files on their C drive. They may have to move things around. And if that's the case, I'll show you a quick and easy way of putting your files somewhere and then remapping the locations those here is using the data source settings cause files often move around on us. So what we're gonna do here is I'm gonna take the order date file and move it out of this directory and put it on to my desktop. So what I'm gonna do here is I'm just gonna quickly open up my File Explorer, like so and I can see in my Dimensions folder that we have here. I've got the order date file. So, I'm just gonna take that and move it over on to my desktop. Just for fun to go ahead and break this. Okay. So now I only have the three files sitting inside Dimensions here. So if I go back over to my data source settings and close this out and now I'm back over on my order date query here. I'm just gonna go up to the home tab under query and then I click on this refresh previous. I wanna take a look at this data. And suddenly what has happened is an error has been thrown. It says I can't find the file that it should be at C MSPressIntroToPowerBI Data Dimensions The query is now broken. So how can we go about fixing that? What we'll do is we'll take advantage of the data source settings here by getting back into the dialog box by clicking there. And we're gonna go to this first setting here on order date. And we'll go down to the bottom here and click change source. So what we're gonna do here now is it's gonna re-prompt us to say, well, where has this file moved to? So I'll click on browse and I'm gonna go over to my desktop. And there's the order date file that I just moved a few moments ago. (mouse clicks) Click open. (mouse clicks) Click Okay. And we'll see now in the actual data source settings dialog box that now the file is sitting on my C drive under C users, Chris Sorenson, desktop order date. Now mind you, if you put on your desktop your file path would look different but that's not the point of this exercise. We actually wanna show you how to move files around. So I'm just gonna go ahead and click close. And what I'll do here now is I'm gonna go back at refresh preview again and click refresh. And now we can see the query is back to life because now we're pointing at the file that was moved over to the desktop. So as I mentioned, this is really handy if you may be one of the people that wasn't able to get the files put on the C drive and you're wondering, well, where should I put them? and how am I gonna do all this free mapping? The data source settings are something that will allow you to do that. So an additional place this can be done and it's on a query by query basis is over here in the applied steps. We can see for this order date query under source, I can click on this little gear to the right hand side of source and it's also then gonna bring up a dialog box that shows where this file's mapped. So if I wanted to go in and do it individually, file by file or query by query, I could go and use that. Or if I wanna look at the consolidated view of all the different data sources I have in my inside a power query here, I could go ahead and do all the work right here. So this is a really handy way to change location files cause often file locations will change. Okay. So the other thing I just wanna point out is the global permissions over here. So if you recall back from an earlier sub lesson where we connected into our web source. We can see the Wikipedia data that we connected into. And if I wanted to I could go down here and say, edit permissions, and I can go in here if I need to do something a little different around those permissions and go ahead and change from anonymous to perhaps one of the other connection types there. But I'm not gonna do that. Just wanted to show you this exists in here. We have the ability to go in and change our permissions. And this becomes perhaps even a little more important once we start getting into, you know, SQL server databases, files that are inside of SharePoint online and maybe we need to make connections to it and use different usernames and passwords and things like that. So the global permissions become very handy there. Right now the current focus is the data source settings that are actually in our current file. Okay. So just wanted to point all that stuff out to you. A nice, easy way to manipulate your different data sources, edit the permissions if you see fit because everything is stored in this one nice consolidated view. Right. So that brings us to the end of this sub lesson.