How to use Power Query to connect to a file on OneDrive or SharePoint (read description for update)

an ever-increasing number of companies and now moving their files to SharePoint and onedrive the days of connecting to files on your network like your old drive or your W Drive and saying get data from Excel or from CSV you know those buttons won't work anymore when your files are stored on onedrive or SharePoint there's different techniques you have to adopt and I'm going to show you two of them now neither of these techniques are particularly good they're confusing I mess up halfway through one of them and it causes me all sorts of grief so just watch hopefully I can teach you from my mistakes how not to do this or how to do this and you'll agree that we all need a better process so hopefully by voting and user voice and I'll attach a link we can encourage the power query team to give us a much easier way of doing this because more and more people are going to want to connect to excel files and CSVs etc that are stored on onedrive and SharePoint so watch the video watch till the end let me know what you think and we'll hopefully have a better way of doing this soon Thanks so per query is awesome at connecting to excel files etc on your network but it's not so good at connecting to excel files and things on onedrive or SharePoint so for example if I go to data get data from file I want to go from a workbook that's fine as long as the workbook is on my network and if I wanted to connect to say this file here now this file is on my onedrive folder if I actually wanted to connect to it what I'd end up doing is connecting to this C Drive which isn't necessarily what I want to do I want to be able to refresh this online not connect to my C Drive so that's not the method you have to use so there's two methods there's the from web method and there's the from SharePoint folder method so I'll do both them to show you the problems of both of these so if I go from web I need a URL so how do I get that URL well this is the this is the sort of crazy thing that you have to do to start with you actually have to open this online to start with so if I go view online it'll then open up this excel file or I could have just said to open the folder location but I'll just do it this way so it's taken me into this file now the temptation is that you need this path or something like this but unfortunately that's not right what you then have to do is open this file in desktop so opening the desktop app okay it says resume editing that's always a bit of a misleading one here so this is now open in desktop or it's actually opening still give it a second here we go so it's open in desktop so how you get the file path is this you go to file and then it's this path here and you right-click or so you just left click copy path to clipboard and then I can close this file down okay so now I can do is go to the throng web and then just paste this path in to the URL path but then the other bit you have to do is to get rid of this question mark web-1 and click okay now nobody is going to be able to discover that without watching a video or an article on it so i want to go with my organizational account because on my organizational onedrive SharePoint folder and sign-in okay now I'm at this stage I can click connect so what I would really like is a button to allow me to do that rather than jumping through all these and Here I am I can get tables tails right click transform and from this point forward it's a normal power query experience in the actual applied step for source you can see it's got this long pretty horrible path but ok it's with all the percentage 20s for the spaces but the navigation is just jumping to the sales and it's pretty simple step and easy to easy to understand and if I go to data saw settings you know it actually shows me the full path of what I'm connecting to and if I could change source you know it's going to the full file path but there's no browse or anything like that I would have to manually change this to the fehb folder if I wanted to maybe point to the same file that's sitting in the fed folder it seems not agree not a great experience but that's the way you do it with the from web okay so let me I love this feature discard in Excel I wish that was in power bi let me just discard that okay so the other way is to go from SharePoint so you'll get data from file from SharePoint folder and again you're prompted for a URL so let me just go back in here and actually resume editing here I just want to jump back to where I where I was ok and I can just click on this folder here at the top left and I'll take me into my onedrive for business and again it's like what path do you actually use for that URL well in this case it's just this one okay now let me use the wrong one and show you what happens let me just go a bit too far okay and I'll put that in and see okay this process then gives you this unable to connect so you go okay I'll edit that and you say okay well what shall I do I'll maybe go that far back and you click okay okay that's work sometimes that screen gets you in a complete loop it's horrid and you have to go to data source settings clear permissions and star again that just doesn't seem to be a way out of it okay so then you attempted to do this combine or load Briscoe transform so now we're into my onedrive so once you know what folder to use which again is totally honest you're into this scenario and I want to get to the data file that I want look if you know what the file is called then potentially you can filter through this list but I've got a data file in each of my folders so I just want to go down to the folder I want and here you're faced with a screen which doesn't show you the path that's long enough so you can't even tell which one we can't even pick which one to filter to so if I search for ones with Jan in them and I'm just say load more okay let's go there is this one but I still can't really see if it's the one I want and I've had two folders with janan I wouldn't be able to see it so one option is to move this folder path across a bit okay and then I can make it a bit wider potentially so then I can potentially scroll down and see a bit more but again the filter box is only this wide and I can't resize that so let me just filter down to that one called Jen to try again okay let's have a look if this is the right one so there's the file date two dot XLS and now from this point again it's not at all obvious why I meant to do but you click on this binary and outcomes the file and then I've suddenly realized this isn't even the right file this is another demo file I set up earlier this one with the overly long formula for demo purposes is not the file I was even trying to connect to I was trying to connect to if I just bring it back up a second I was trying to connect to this one on my onedrive folder and demo sessions and their onedrive and SharePoint demo source files Jan so it's this one I'm trying to connect to so again it's just confusing let me try this then so let's right I'm gonna give this another go so I want to connect to this file that's in demo sessions onedrive and SharePoint the source file is Jan this one so I'm gonna go and grab this URL and go back into Excel get data from file from SharePoint folder ok I'm gonna paste that in there click OK transform it's gonna refresh this query okay it's saying they were access to resources a bit so edit settings it's remember the fact that it doesn't like my credentials and this is where I was talking about you have to clear out these credentials so I go data source settings clear permissions and click close then try refreshing and hopefully this time it'll probably there you go edit credentials let's go Microsoft account of sign-in okay so let's try this again I'm gonna remember this is the file and they just bring it up here so onedrive and SharePoint demo so let me try and filter this live more okay so at least we're down to these four folders but again I can't tell which one is which but let's just filter down to that as a starter and there's three is three different data files okay and if I click on one of them down the bottom of my screen I can actually see the full path that's the Feb one that's the Jan one okay great so with that this is the one we want I'll just use this old trick I'll go I can go right click and I can go text filter equals if I wanted to or I can just come over straight over here to get the data I can just click the word binary and that will jump into that SharePoint file so this is the way to connect into a file on SharePoint and again if it was just a button it would be way way easier so here's the table then I've got to drill into the table and then finally from this point forward is the normal power query experience now this way you know the source is this full path then I've done this filtered row step which I could probably delete now to be honest I don't really need it and then this step is where it's actually going to but that's the path and then there's the file name okay but if I go to datasource settings there's nothing telling me I'm connected to a specifics file here and if I say change source and just get a browser to that point so there's no easy way of me tapping into that folder I've just can't use this for that I have to do it via this little UI here so for me you know that's the problem that's why we need a nice button and if I close and discard this that's why we need a nice button that just says here get data from file from file on SharePoint and that would work with Excel text and CSVs you know that would be awesome just to avoid all that annoying hassle there is one more trick we can do with the from SharePoint folder and pick this one up off an article done by McGraw lescovar if you haven't come across Miguel Escobar and you're learning power query then you're missing out so Miguel does some awesome stuff so let's go get data from file fom SharePoint folder in fact I've already connected to the SharePoint folder so little trick recent sources don't forget you've got this if you're always tapping into the same SharePoint folder or the same onedrive folder you'll have these recent sources here okay so I'm just gonna click on this double-click and away we go so this little trick is all about making the navigation a little bit easier potentially with the SharePoint approach and it get to this stage like I did early on the video and we go for a transform data so we're in exactly the same sort of spot where we've got this issue with the really long folder paths going on the side etc and I listed everything down here now the little trick is up here where it says files you actually change this to contents and see the little intellisense kicks in so you can just double click or press the tab key and then press enter and it actually get a little bit of a different experience now so from here you then look for the line that says documents and you can go to table and that then drills down and this is more like a folder drilling down experience so from here I'm getting into all the folders that are available and then further down I've got some musical binary these are the actual files there in that folder but these are subfolders so if I go to my demo sessions I click on that table and here's the onedrive and SharePoint demo so I can click on that and we can then sort of drill down this way to get to the actual files were after and even if you click to the right of the word table down the bottom of your screen you can actually see a little list of all our subfolders now the problem with this approach will not really a problem but just a bit of an annoyance is if you go down the wrong path to the wrong table there's no getting back a step you have to delete the navigation step and start again so I've got to go to documents and then into my demo sessions demo sessions and so on so there's no going back and so if you've got a nested sort of folder then it's a bit of a problem but you know none of these solutions are particularly great that's why we need to vote for an easier way of doing this and I'll make sure there's a link there in the bottom so we're going into the onedrive and SharePoint demo into source files so this navigation is potentially a little bit more user-friendly than going through all these folder paths and things into the jam folder and then I click on the binary so it's okay now be aware that you can really have problems if you've got really long nested formulas especially if a folder path there's more than 256 characters when you're using Excel just does not like it so from the table sales I can go table and Here I am I can now do my normal power query stuff so the source is the folder the navigation is the name of the file which is a little bit easier I think than the the other way of approaching this got this imported Excel step and into the table so that I think the navigation steps are easier to read and if I go datasource settings again we still got this thing of there's no file there you've got this navigation step that's data you've got the source that's this but nowhere in here is actually the file path and if I go to advanced editor it's sort of in here source name documents demo sessions demo sessions so it's in the advanced editor is the sub files but none of that actually displays in the window here okay so it's a little bit different not quite the same guess that whole step just doesn't really show it but if you go advanced editor you get to see all these extra set of steps here so depends on how you want to approach this but none of these solutions are particularly good so there you have two different ways well three if you include Miguel's SharePoint contents method of connecting to files when you start storing them on SharePoint and onedrive I think you probably agree that this is a painful process it's confusing it's fiddly and ideally there should be something a lot simpler so wherever I post this I'll also include a link for you to click on and go to user voice and vote to get a simpler way of doing this let's all hope that this gets easier quicker stay safe and we will get better at power query together Thanks

Channel: Access Analytic

Views: 47,985

Rating: 4.8990536 out of 5

Keywords: Power Query, OneDrive, One Drive, SharePoint, Share Point, Power Ni, Excel, PowerBI

Id: igcCbKqtwrk

Length: 20min 0sec (1200 seconds)

Published: Sun Mar 22 2020