Power Query to Extract SharePoint List Data using Excel

Microsoft Power Query is a free add-in for Excel – Part of PowerBI that provides self-service Business Intelligence features. Lets see how to extract SharePoint list data using Power Query for Excel.

Step 1: Download & Install Power Query Add-in

To start with, Download the Power Query Add-in for Excel from: https://www.microsoft.com/en-us/download/details.aspx?id=39379

Once installed, You’ll find a new tab “Power Query” tab added to Microsoft Excel.

power query and sharepoint 2013

Step 2: Connect to SharePoint List

To extract SharePoint list data, click on the “From Other Sources” button from the ribbon and choose “From SharePoint List”
power query excel 2013 sharepoint
Provide the URL of your SharePoint site and choose the Authentication method accordingly – I Selected Windows. For Office 365, choose organizational credentials.
power query connect sharepoint list
Once you are successfully logged in,  You’ll get the Navigator page to choose your source list and click on “Load”. By default, Only Lists appear in the Navigator.  Pick any list and use “Advanced Editor”, change List name & “SharePoint.Tables” to “SharePoint.Contents” to get data from any library!
power query connect to sharepoint list

Step 3: Remove Unnecessary columns, Perform ETL

This loads all columns from the SharePoint list. However, we may not be interested to fetch all columns. Also removing columns that are not required speeds up our data retrieval. So, Right-click on the Query from the Right side pane, Choose Edit.
power query sharepoint 2010 list
You’ll get a Power Query Editor window where you can:

  •  Add remove columns to your query –  in order to focus only on what we need
  • Merge-Append (Join-Union) from other lists
  • Perform ETL operations such as replace values, Split Columns, Group by etc.

power query for SharePoint
To Remove unnecessary columns, click on “Choose Columns” button and select only the columns that you need. Once done, click on “Load & Close” button from the ribbon. All activities inside the query editor are recorded as Steps. We can remove/Re-order them as required.

excel power query SharePoint list

To get actual values of Lookup columns, You’ll have to “Expand” them. Once ready, click “Close and Load” from the Query Editor ribbon, and the list data will load to your workbook,

excel power query sharepoint list

Although “Export to Excel” can retrieve data from SharePoint list, Power Query has much more capabilities.

So next time, without the need to open SharePoint site in browser, You can just open the Excel file and Hit refresh! Once the data is loaded, We can utilize advanced features such as: Pivot Table, Pivot Chart, etc to output a variety of visual data in Excel worksheet.

I used this Power Query tool with SharePoint 2010 and SharePoint 2013.

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

2 thoughts on “Power Query to Extract SharePoint List Data using Excel

  • January 31, 2019 at 8:02 PM

    This article is very helpful. I just followed the instructions and it was a breeze.

    Reply

Leave a Reply