I'm going to show you how you can use Power BI desktop and connect the data coming from a SharePoint folder or a specific SharePoint file or Contents off a SharePoint list. Let's get started so here I have my SharePoint file and I have some folder structure in there and in there I have some files that I would like Power BI to connect to so that I can set up a one click refresh or Automatic refresh so that's the first thing that I have. So let's start there connecting to a SharePoint folder So I'm going to switch over to my Power BI desktop Window Here. I am and I'm gonna click get data and click more and and again that was I had clicked get data and in that the more dialog box and that should bring in this dialog box up and here What I would like you to do is just type in cher or SharePoint if you like and you would see the SharePoint folder option right there and I want you to hit connect and Now it's gonna ask for a site URL for that I'm gonna go to my You know SharePoint site again, and I'm gonna copy the whole URL here and just come back and paste it in here although if you do that You're gonna notice this warning and it's gonna say that Oh The URL isn't valid and it's saying please enter the site's root URL only Actually, if I had paid attention even information I can is talking about this root URL. So what is that talking about? So what it's talking about is that you know, it doesn't need this whole URL It really needs just the root URL which points to your site. It'll probably look something like this So keep your site name in there, but yeah trim everything else out And at this point you're gonna hit OK while that's coming up. I just wanted to introduce myself. My name is Avi Singh I'm a Microsoft MVP and best-selling Power BI author And if you want to become a Power BI pro, make sure to subscribe and click that bell So you are notified whenever I go live to answer your Power BI questions. Alright our dialog box came up And this is where a lot of people get lost because the Staubach comes up is like gosh. What is it showing? it's showing me some kind of files and the button that it see - want you to click is combined, but that is absolutely The wrong button to click you do not want to click this button Bad things will happen well you'll see so you don't want to click that button instead the button you want to click is the Golden a transform dia. In fact, that's the one shouldn't that should have been color I'm going so let's go ahead and click transform data and what it's gonna do is it's gonna launch the very editor and by the way If you're just starting out or you seem to keep getting stuck in power bi then I'll also link to my power bi tutorial in this video Alright, so here you have and what is what is going to show you? It's gonna show you all the files in that SharePoint site Now, I'm pretty sure that you would not want to pick up all the files from there so what you want to do is zero in on this folder path option and and Specifically select the folder only the folder under which you want to get the file So what I'm going to do is in here and this is a demo scenario So I don't have to how many folders but either you can select the folders or you can use text filters So, let me just show that to you really quick So what you can do is go in here and text filters and you have a lots of different options You can say the folder path needs to contain a specific keyword or begins with this or something like that, right? So You see or of course, you can do an exact match as well equals this for now what I'm gonna do is I'm just gonna select this products folder and hit OK and Even after that it's possible that within that folder you might have a whole bunch of files and in this case Maybe I only want to get the excel files and I do not want to get the CSV files So you have all of that available to you? in fact if you really wanted to you can even Do the similar kind of filtering on the file name and you can say hey only give me the files which begin with products Underscore or something like that or containing, you know kind of something So for now, what I'm going to do is I'm just gonna filter by extension keeping the excel file and hit OK alright So now that you we have exactly the files in the folder that we wanted and only the list of files now, finally We're ready to combine and get the data and you do that using what I call the action button over here So again, if you if you zero in on this notice if you hover over this action button It says to combine for us and that's what you want to do So I'm just gonna click it and it's gonna take a little bit and I'm gonna be right back Alright, so partly I came back with this now Usually if the files are well structured and they all are of the similar structure I'm assuming they are if you're trying to come behind them together, then you can pretty much accept the defaults here You know you can tell it which sheet you want to bring that in and just hit OK and It'll pull in and combine all those files Alright bar BIA is back. And when it's combining Excel files, it does create this sub structure, which I wouldn't go into now But it has already done the right thing for you and here you can see We have all the excel files in a folder and I can see the contents of the files have all been combined I'm just gonna give it a good name. Let's caller SharePoint folder Files. Alright, so that is our step one now Let's move on to step two, which is how do you connect to a specific file on SharePoint? So in this case, we're not maybe in some scenario. We don't care about the folder or all the files in the folder Maybe we just want to focus on just one specific file so for that the way I like to do is Is do the trick is to open this file in your desktop app Once the file opens up in your desktop app You can go to the file tab in info option and click on copy path again You want to go to file? info and click on this copy path button once you do that and I'm gonna just stick that into a notepad over here and This gives you the bat of the file. Now if you're you know, if you really wanted to you could probably Write by hand as well, but I that's you know, I just copy Bethel like that But there's one key change you must make if you want any hope of this to work Which is this last bit this question mark web equals to one that needs to go so you want to delete that and and that bath is good enough now, I'm gonna we're gonna go back to our Barbie I file which is here Actually, I'm just gonna stay in the query editor and in here I'm gonna say new source and this is where sometimes I get wrong I'm Pretty sure if you try Excel it doesn't work and I think the the better option or the one that I use is I say Web so get this from web. So I'm going to click that and base that URL that I just got that by copy path and editing in and That should be able to perfect that got the excel file and it's gonna show you all the sheets in this case I just have one sheet and I hate okay, and that is the way to connect to a specific SharePoint file Well on to our last one, which is to connect to SharePoint List, so here I have Our students list and we got Batman Cyclops clash and we have the awesome doesent index. Oh my gosh Some of these rankings should be higher. But anyway, so this is our list. So let's go back to power bi desktop And and we're still in the karate editor. We're gonna stay here. So we're done the queries and in this case I'm gonna click more again get new source more and I'm going to type in SharePoint again to get to the Option for SharePoint list now my I'm saying is a SharePoint list is for an on-prem list This is the one that we using SharePoint Online list. This is on a SharePoint Online site So you hit OK and it's gonna feel similar to the folder. So now you you see the site URL And again, it's similar to that by the way There was one step that was not shown earlier because I'm already signed in the first time you do this it would ask you for credentials Alright. So again, the URL is similar to how we specified it before it is just the site URL I often copy/paste the whole thing, but it doesn't really need all of that it only works if you give it the root URL so we have that set and I'm gonna hit OK and And difficult that's what I was waiting for. So this Sometimes can be confusing. You don't want anonymous for sure You don't want Windows you you typically want what is shown as Microsoft account? Times. I've also seen this being shown as an organizational account but either way, that's the one you want and I'm just gonna sign in All right, so I'm signed in and I'm ready to go. It has my credentials I'm gonna hit connect. And again, it had my site URL But now what it's gonna do is gonna look through the site and show me all the lists that are available now It does show you some of the system lists that are available as well. Now. I had no idea I had all of these lists on there The only one that had created was this one probably a class list But let's look into that and and and again once you click on this the first time I did this I'm like wow This is not my list because it does show you a lot of system columns as well But I'm glad well, you know the content stands out so awesomeness index. So this definitely is the list that I'm looking for But again the first time you do it it might look really confusing you can see it has lots and lots of different columns But for the most part you can ignore though. Somebody's just gonna select that hit. Okay and once that loads what I'm gonna do is I'm gonna keep those two columns that I care about so only get about This title and this awesomeness index and I'm just gonna right click and say remove other columns. So there you go my friends So we have connected to a SharePoint folder And you've seen all the gotchas all the tricks and to connect to a SharePoint file and to a SharePoint list now I'm gonna hit close and apply and hey Make sure to click on the screen to watch my next video and also check out my power bi Tutorial and don't forget to subscribe click on the circle thing here on the screen and I'll see you next time until then Bye Ron Hey keep watching more videos and keep learning power bi but if you did enjoy this video I would love to hear from you. So leave a comment like subscribe All the good stuff while we're on my friends
