Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Extracting a SharePoint List with Power Query


By:   |   Read Comments (6)   |   Related Tips: More > Microsoft Excel Integration

Attend these FREE MSSQLTips webcasts >> click to register


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.

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.

Navigator

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.

Preview

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

Thanks!

 

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

 

Regards!

 


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

Hi,

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?

Regards,
Koen


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

 hello,

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

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

http://blogs.msdn.com/b/powerbi/archive/2015/09/23/44-new-features-in-the-power-bi-desktop-september-update.aspx


Thursday, July 02, 2015 - 8:07:44 AM - Nicolas S. Back To Top

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

Hi Koen,

Great post, I liked.

Regards


Learn more about SQL Server tools