Extracting a SharePoint List with Power Query

By:   |   Comments (10)   |   Related: > Microsoft Excel Integration


The tip Introduction to Power Query for Excel introduced us to the world of Power Query, a self-service ETL tool of the Power BI family. With this tool, a lot of different sources can be extracted into Excel with just a few clicks. This tip will show you how you can read a SharePoint list with Power Query and how you can extract its contents into Excel.


If you haven't installed Power Query yet, you can download the free Excel add-in. For a general introduction, please read the tip Introduction to Power Query for Excel. In this tip, I'm going to extract data from a SharePoint list located locally in a virtual machine. It contains a few Olympic disciplines and the sport they belong to.

The SharePoint list

Reading the SharePoint List

To read data from a SharePoint list, go to the Power Query ribbon in Excel, select From Other Sources and choose From SharePoint List.

PQ ribbon

The first step is to enter the URL to the SharePoint site. This doesn't have to be the exact URL to the list, the site itself is enough.

Enter URL

The second step is authentication. Depending on your set-up, this step behaves differently. If you can sign in into the SharePoint site using your current Windows credentials, this step might be skipped automatically. In my case, the SharePoint server is in another Active Directory, so I need to confirm how Power Query needs to access the SharePoint site. However, Anonymous and Organizational account are not supported, although these options are listed.

Windows authentication

At the time of writing, the Power Query user interface currently has no option to specify in a different Windows account, but this might change in the future. When you hit Save, Power Query tries to connect with the current Windows account, which gives the following error:

Windows authentication error

A work-around is to first go to the SharePoint list with Internet Explorer. The browser will prompt you for credentials.

Enter credentials

When you select Remember my credentials, the Windows user and password are stored locally on your computer and Power Query can use those to log into the SharePoint site. Another option is to save your set of credentials directly into the Credential Manager in the control panel.

After Power Query successfully logged into the SharePoint site, the navigator will display its contents.

The Navigator

To view the SharePoint List contents, simply select the list and click Edit. This will bring you to the Power Query editor where you can use the full power of Power Query to manipulate the data. An interesting option is to include more metadata from the SharePoint list. There is for example a column CreatedBy, which is actually a link to a record that contains more information of the person who created the SharePoint list. When you click the double arrows, you get a list of columns that you can include in your original SharePoint list, such as the name of the creator.

Bring in extra columns

There is however a lot of metadata columns in a SharePoint list that are not immediately useful, so it makes sense to keep only the columns that you need. The easiest option to hide a lot of the columns and select only the columns that you need, right-click and then choose Remove Other Columns. This option makes sure that if new columns are added to the SharePoint list, they will not be included when the query is refreshed.

Remove columns

If you have to select a lot of columns and they are hard to track down, a good alternative is to hit Choose Columns in the Home ribbon. This will give a pop-up where you can easily select the necessary columns using checkboxes.

Choose columns

All that is left is to save the changes in the editor menu and to load the data into Excel.

The result

Working with a non-English SharePoint List using OData

When your SharePoint website has a different locale than English, it's possible Power Query has issues reading the metadata, where you end up with an empty navigator. The problem is described here: Unable to import Non-English SharePoint Lists. Hopefully this gets fixed in a future release of Power Query, but for the moment we have to use a work-around: reading the SharePoint list using OData. You can find the From OData Feed import right beneath the SharePoint List in the From Other Sources dropdown in the Power Query ribbon.

The first step is to enter the URL to the OData feed. This is your SharePoint site with the following path attached: /_vti_bin/ListData.svc.

Enter URL

The second step is again specifying the credentials. In this example the Windows credentials were used again.


When Power Query successfully reads the OData feed, the resulting metadata is displayed in the Navigator. This time there are a lot more lists to read from, but your SharePoint list should be present as well. By selecting it and choosing Edit, Power Query is opened and there is no difference as when we were reading the SharePoint list directly.



In this tip we showed how easily Power Query can read a SharePoint list. You can authenticate using Windows credentials. If the SharePoint site is a non-English site, you can read the list using OData instead.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, August 16, 2021 - 10:49:39 AM - Koen Verbeeck Back To Top (89130)
Hi Wille,

I suppose your colleagues need read access to the SharePoint List as well.
What is the error that they get?


Sunday, August 15, 2021 - 12:19:49 AM - Wille Back To Top (89128)
Hi Koen,

I had no Problems With the Query, But when other employees want to refresh the query there is a error. Do you know if this is a general topic?

Many thanks

Friday, July 31, 2020 - 11:19:14 AM - Koen Verbeeck Back To Top (86228)

Hi Aaron,

is this one of your own columns in the list, or rather a metadata column?


Thursday, July 23, 2020 - 12:40:29 PM - Aaron Anderson Back To Top (86181)

Is there a way to get the field name as it appears within sharepoint rather than an ID? I believe the issue is that sharepoint has a userID list and the Person Group field does a lookup on that list so it displays the name in sharepoint but when importing to excel its pulling in the ID field.


Monday, September 11, 2017 - 2:00:23 PM - DMarq Back To Top (66180)



This resolved my problem. After a long list of visited sites .




Wednesday, July 26, 2017 - 4:38:59 AM - Koen Verbeeck Back To Top (63269)


I'm sorry, but I don't really understand what your problem is. Something with a missing list?
Can you try to elaborate a bit more?


Tuesday, July 25, 2017 - 1:49:46 AM - anonymous Back To Top (63200)


I did all the steps and everything went right. But when i selected the list in needed i saw that it wasn't the same list that i needed. so i opend al the list but non of them was the correct one.

Do you have any idea of what i should try next?



Thursday, September 24, 2015 - 2:23:39 AM - Koen Verbeeck Back To Top (38743)

The issue with non-English SharePoint Lists has been resolved:


Thursday, July 2, 2015 - 8:07:44 AM - Nicolas S. Back To Top (38107)

Great workaround! The actual fix from Microsoft remains awaited though.

Friday, May 15, 2015 - 9:44:58 AM - Junior Galv„o - MVP Back To Top (37180)

Hi Koen,

Great post, I liked.


get free sql tips
agree to terms