Power Query: Get data from SharePoint lists | Excel Off The Grid

SharePoint lists are a common data source for many organizations and with the increase of users with Microsoft 365 licenses I think that can only continue and it's not just SharePoint so teams OneDrive and lists are all built on that SharePoint platform so actually we might be interacting with SharePoint without even knowing it so if we've got our information there in SharePoint the question is how do we get to it with power query and that's what we're looking at in this video so if you're ready let's get started the first thing we need to do is to get the URL of the SharePoint site so here I have a SharePoint list on the screen and in the address bar at the box you can see the full URL for that list however we don't want the full URL we just want the section that goes up to the word after sites so I've got https my SharePoint site address then the website and then the name of my site that's the bit that I want to copy so press Ctrl C because that's the magic element that we need for power query the next step is to get that data into Power query so here I am in Excel I'll go to data get data now SharePoint lists exist in two places if I go to from online services you can see the first item is from SharePoint online list or if we go to from other sources there's an item that's called from SharePoint list now the differences are that from SharePoint list will connect to SharePoint on-premise and SharePoint online while the other option of SharePoint online list only connects to SharePoint online the method we choose also determines the type of data that we can get out from SharePoint to start with let's select from SharePoint online list in here I'll paste the URL that I previously copied now we have an implementation one point naught or two point naught if we expand the advanced options you can see that 1.0 doesn't give us any additional options but if we select 2.0 we can decide to select default or all so default gives us the default view for that SharePoint list or if we go to all it gives us everything from that SharePoint list because somebody could change the default view I think it's worth selecting all that makes sure that we get all the columns I'll click that and then click OK if this is your first time connecting to SharePoint through Power query it will ask for your credentials so I'll go to my Microsoft account and then click sign in okay so now ask me to sign in select my email address and then I can just click connect the Navigator window appears and it gives us all of the tables that exist within that SharePoint site a lot of these at the moment are for sharepoints internal workings but if we know the name of our list which is PQ example we can select that and then click transform data okay that's now loaded into our preview window you can see that we have our additional data and we also have some additional columns and some of this information is useful it gives us the date modified and the date created and there's lots of additional fields in there some of them are null some of them probably contain some useful information but really what we want is our original table so we can select those columns you can decide if we want the ID column or not I'll include it this time then from the home ribbon you can go to remove columns remove other columns then we can close and load that into Excel now what view would we get if we didn't decide to load all the fields but instead we decided just to load the default view once we come to source you can see that we have View mode in there we can change all to default and this is the view that we would have received had we selected the default view in the connector window so we get these fields here if I delete the remove other columns you'll see that the only fields we get are that title column all the way to our ID column so deciding between defaults and all determines whether we get these additional columns or not right I've got my columns selected let's go transform and then detect data types so title category project ref fees are all text recoverable fees the cost to complete are whole numbers they should probably be currency I'll replace that current step status is text and then ID is a whole number so we can go to home close and load close node two I'll load this as a table on the existing worksheet in cell A1 and then click ok write the best now loaded the information from SharePoint so now if we come back to SharePoint I'm going to select one of my values and edit that value so let's say it's 2 600 that's recoverable fees and cost to complete is four thousand six hundred I'll click save on that list then I'll come back to excel from the data menu and then click refresh all fantastic and now power query has updated to show those new values but before you head off there's two other things which I need to show you and make you aware of we've just seen how we can use the SharePoint online list connector now let's have a look at the SharePoint list connector so from the data ribbon go to get data other sources and then from SharePoint list I can paste in my URL and then click ok this loads the Navigator window in the same way from there I'll select PQ example and then click transform data once this loads in the preview window you'll notice that we have not just a few more columns that we had before but we have a lot more columns so by using the SharePoint list connector we get access to a lot more information you can see we have our title a date modified and the date created they're the same as we previously had keep moving to the right we get our data that we actually had in the table we might need to rename our fields and if we keep scrolling we eventually get to the author and editor Fields if I select any one of those records you can see that it gives us a lot of information about that record so who the author was what their email address is you can see that there's a wealth of information in here and that's the same with editor as well you can see all of that data about that record we can even expand this column so I don't want all those fields let's say I want email I can then click OK this then tells me the email address of the last person to update this item in SharePoint there is an alternative method that we can use and it doesn't use power query so if we're not undertaking any other Transformations within power query on our SharePoint list this other option may be a better option I've got my list open I'll come to export and then click export to excel if I come to my downloads folder you'll see that this has downloaded a query dot iqy file I'm going to double click this to open it up gives me a warning message to say that this file has data connections I'm going to click enable and I'm going to select table and the existing worksheet and I'll click OK this now loads my SharePoint list into Excel without going through Power query but this table has a connection back to my SharePoint list so I'll come back to SharePoint let me edit this value again so recoverable fees of 12 600 and cost to complete of 14 600. I'll click save then back in Excel from the table design or even from the data ribbon and click refresh all that will then update this table directly from the SharePoint list if I no longer want this table to be connected to my SharePoint list from that table design ribbon I can just click unlink and that will then break this link okay and now you can see the refresh button there is grayed out because this table is no longer connected directly to SharePoint so that's it that's four ways that we can get data from a SharePoint list into Excel and all of those methods update just by clicking refresh three of them involve power query and we can change the connect or the settings forget the columns that we need or we saw that we can have that iqy file that downloads from SharePoint we can open that up in Excel and it gives us a live connection back to our list so hopefully you like this video if you did don't forget to subscribe and if you want to know more about power query and how it can help you connect to other sources then check out our training academy over at excelofthegrid.com forward slash Academy thanks for watching and we'll catch you next time

Channel: Excel Off The Grid

Views: 13,081

Keywords:

Id: ZPe7wn91J9w

Length: 9min 48sec (588 seconds)

Published: Thu Mar 23 2023