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.
To extract SharePoint list data, Click on "From Other Sources" button from the ribbon and choose "From SharePoint List"
Provide the URL of your SharePoint site and choose Authentication method accordingly - I Selected Windows. For Office 365, choose organizational credentials.
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!
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 which are not required speeds up our data retrieval. So, Right click on the Query from Right side pane, Choose Edit.
You'll get 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.
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.
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.
You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.