By: Koen Verbeeck | Comments (10) | Related: > Microsoft Excel Integration
Problem
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.
Solution
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.
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.
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.
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.
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:
A work-around is to first go to the SharePoint list with Internet Explorer. The browser will prompt you for 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.
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.
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.
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.
All that is left is to save the changes in the editor menu and to load the data into Excel.
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.
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.
Conclusion
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
- For an introduction to Power Query, see Introduction to Power Query for Excel.
- In the tip Using the New OData Source in SQL Server Integration Services, you can read how to read OData feeds with Integration Services.
- If you want to know more about Power Query and the M language, check out the book Power Query for Power BI and Excel by SQL Server MVP Chris Webb. Since Power Query is frequently updated, some parts may be already a bit dated. Most likely the user interface has changed the most, but most of the M language is still unchanged.
About the author
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
