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