Get Data From SharePoint or OneDrive with Power Query - Demystified!

nowadays many of us are saving our files to the cloud using onedrive for business or sharepoint online unfortunately getting data from these sites using power query is not so straightforward so in this video i'm going to step you through the three scenarios getting data from an individual file on onedrive or sharepoint getting data from a sharepoint folder and getting data from a sharepoint shared library let's get started we'll start with getting data from an individual file on onedrive or sharepoint either way the process is the same and it requires getting the file path but not the file path as you know it now if you're like me you'll have your onedrive files synced to your hard drive you can see mine here in the file explorer and if i copy the path by the home tab and then copy path and then go to notepad and i'll just ctrl v to paste it in you can see the file is on my hard drive because it starts with c colon backslash now this is fine if you only ever want to use this query on your pc but if you want to use this file on another pc or share it with other users you'll want to get the onedrive file path now to do that we go to onedrive for business in our browser so let me open that up so here's the file i want to get now you think i can get the file path by right clicking and copy link let's do that i'll close that and we'll go back to notepad and let's paste in what it gives us that gives us this long link and it looks right but i'm telling you now it's not the link you want so the way that we get the link is to open this file in the app the desktop excel app so here's the file open in excel and then file tab info and i can copy the path here so let's do that we'll go back to notepad let me paste it in to show you what it looks like so you can see the link i get here is quite different to the link that you get if you right click the file in sharepoint this is the link that i want so with it copy to my clipboard ctrl c i'm going to close this file i don't need it open and then in a new excel workbook i'm going to get the data from the file saved on onedrive for business so data and then from web so it's counter-intuitive you think you're getting the data from sharepoint or onedrive which you are but we use the web connector when we're connecting to just a single file so i'm going to ctrl v to paste in the file path now the bit at the end question mark web equals one i need to delete that if you leave that in you'll get an error i'll click ok now it's asking me how i want to log in and here i need to choose my organizational account and it's asking what level to apply the settings to you can choose the root of the directory or you can choose a branch higher up i'm going to leave it at that level for now it says i'm not signed in so i need to click sign in choose my account enter my password and then sign in and click connect and here's the file there's only one sheet in it you can see the data there all looks good so i'm just going to go transform data because i only want to save this as a connection and there you have the data if we look at the source you can see it's used excel workbook web.contents to get the data and then when i've clicked transform it's added these steps to give me my final data set so you can see it's fairly easy to connect to a single file on onedrive for business or sharepoint you just need to make sure you get that file path by opening the file in excel for the desktop and then you use get data from web let's close and load that we'll just close and load it to a connection and we'll look at getting data from a sharepoint folder so i'm going to use this folder here you can see there are three workbooks in the folder now to connect to this folder i can copy the first part of the file path from up here i need everything up to layouts so i can just select it ctrl c to copy so with that copied to my clipboard i can go back into excel got a new workbook here we're going to start fresh and here i want on the data tab get data from file from sharepoint folder and in here i paste in the site url and click ok again it's going to ask me to authenticate so i'm going to use my microsoft account it's telling me i'm not signed in so i'll sign in choose my account enter my password and now i can connect now with this you get a list of every file and folder on your sharepoint site so here i need to transform the data so that i can filter out the files and folders that i don't need i'll start by moving the folder path over to the left because the folder path can be quite long i need more room to see it so let's make it as wide as it will go now if you're like me you have way too many folders to sift through so the best thing to do is filter the list with some of the folder path so we go back to sharepoint you can see my folder path is training training content syllabuses moth excel power query lessons practice files so i can string together just a few of these to reduce the list so i'll use excel slash power query lessons practice files so back in here i want to filter text filters contains and then here i can enter the path now remember power query is case sensitive so you need to make sure you type this exactly as it appears in sharepoint i'll click ok now if you have a lot of files and folders this could take a few minutes okay that's a bit more manageable 79 rows and if we scroll down i think this is the folder that i want i can't make the column any wider but if i click on it i can see the full path here so that looks correct so i'm going to right click text filters contains and that's just going to filter my list down to just hopefully the three files that are in that folder and there we are so now i want to combine those three files just like combining any other files from a folder you click the combine files icon on the content column if you just wanted one file you could just click the binary there that would give you just one i want all three so i'm going to combine files at the combine files dialog i need to choose which item in the file that i'm combining so you can see there's a table and then a sheet called pq2.05 underscore october that will be probably the sheet that contains the table yes looks the same sheet one i think is empty yep and aux data is a named range i'm going to go with the table and then click ok power query now goes away grabs the three tables from each file consolidates them into one table ready for me to work with so there we have the data and if we look at the month drop down you can see there's data in there for all three files it's connected to my sharepoint folder so i can refresh this query and get updates as needed for example when new files are added so let's close and load that one to connection only and we'll look at the last example which is getting data from shared libraries on sharepoint if you work with shared libraries in sharepoint online then the process is a hybrid of the first and second examples here i'm in our my online training hub team site you can see it's a shared library now i want to get the data from the example data folder here now there's two ways you can get the url one is to copy the domain from up here but you need to remove the hyphen my before you use it or you can right click open the file in the app which is opening it in excel on the desktop and again we go file info and you can copy the path if we take a look i'll just paste that path into my notepad you can see it's omitted the hyphen my that you can see in the browser so this is the correct path if you want just the file then you need just this bit without the question mark web equals one if you want the folder then you want this section so copying that we'll go back to excel and i'll close that and in a new workbook we'll go data get data from file from sharepoint folder so again it's still a sharepoint folder pasting in the url remember if you copy it from the team site from the browser you need to remove the hyphen dash my from the url before using it so there it is there without the hyphen dash my i'll click ok it's going to ask me how to connect i want to use my microsoft account i'll sign in choose my account paste in my password and connect i don't have many files and folders in this site it's just a demo site let's transform the data so i can filter out what i don't need we'll move the folder path across so we can see it make it a bit wider so there's my folder that i want if i just want the one file i can click on binary and drill down so let's take a look at that so here's a list of the items in that file i've got a sheet called pq underscore 2.05 november i've got a sheet called sheet1 and i've got a table called table1 if you click in the white space beside the table you get a preview at the bottom of what's contained in that sheet so you can see the tables there this sheet is empty and this is also a table notice this table correctly displays the headers as headers whereas this one when we look at the sheet has the headers underneath so i want this table i can click on it to drill down and now i have the table so that's getting just one file from that folder but if we want to delete these steps we can go back if i want all of the files in the folder again i can filter just to keep that folder and then i can click on the double down arrow to combine all of the files in the folder works the same as the second example we get a preview looks correct click ok and there's my data if we check we've got three files worth three different months so there you have the three ways to get data saved on onedrive for business or sharepoint just note that this won't work with onedrive personal accounts now if you want to share your files with other users you just need to make sure they have permission to access the file folder or shared library so back in onedrive or sharepoint you can do that by going to the file right clicking and share or if you want to share the folder then you do that at the folder level right click share and in here you can choose who can access the file so you can name them specifically so for example i can share my folder with phil and that's going to allow him to open the excel file that contains the query and refresh that query now when phil opens the file he's going to need to set his log on credentials he should get a warning below the ribbon in yellow that says he needs to log in and he can click the button to do so you can also edit your credentials on the home tab of the ribbon in the query editor and then data source settings and in here you select the source that you want to edit click on edit permissions and in here you can change the privacy level and edit you can see here i'm currently signed in with my microsoft account phil would need to choose sign in as a different user it would sign me out and then he could choose his account and log in so that's connecting power query to files saved on onedrive for business or on sharepoint and sharepoint folders or even in sharepoint shared libraries i hope you found these techniques useful you can get the step-by-step written instructions at the link in the video description now i need to give a big thanks to fellow microsoft mvp wynn hopkins who demystified connecting to sharepoint if you like this video please give it a thumbs up and subscribe to my channel for more and why not share it with your friends who might also find it useful thanks for watching

Channel: MyOnlineTrainingHub

Views: 36,260

Rating: 4.974359 out of 5

Keywords: sharepoint power query, onedrive power query

Id: rcYRcsDjPMI

Length: 14min 0sec (840 seconds)

Published: Thu Mar 18 2021