If you want to extract all data from a SharePoint List to a Excel workbook, an easy solution is to use Export to Excel.
SharePoint allows you to export a Web Query that downloads that list data into an spreadsheet.
Besides that, there is another way to extract data from SharePoint without the need to open a web browser and access the SharePoint site.
It’s called Power Query for Excel and it’s designed to help by easing data import from a variety of sources.
It allows us to model the data in different ways, like filtering and grouping before we import it into Excel.
The Export to Excel feature allows us to do that, after we insert the data into the spreadsheet.
In this article I will use both methods to extract data from a SharePoint List and I will let you see the differences (some of them) between Export to Excel and Power Query.
Export to Excel
From the LIST tab click on the Export to Excel button.
When we run the Web Query, Excel asks us for permissions, in order to connect to an external data source.
Here we must click on Enable.
After allowing Excel to connect to SharePoint, all the data from list is downloaded into a spreadsheet.
Microsoft Power Query for Excel
According to Microsoft:
Power Query provides an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more. Power Query also provides you with the ability to search for public data from sources such as Wikipedia.
Download Link | Power Query
From the POWER QUERY tab, click on From Other Sources and then choose From SharePoint List.
Next, we must specify the URL to our SharePoint site.
One important step is about the authentication to the SharePoint site.
Here we can connect:
- Using Windows credentials;
- Using MOS ID.
Because I have my Office 365 account connected to Office 2013, in the MOS ID section I just click on Sign In and I am automatically signed in.
In the bellow image we can see that the data is downloaded (101 KB when I took the screenshot).
In the Query Editor we can see the data from our SharePoint List.
Here we can:
- apply a filer;
- sort the data;
- hide the formula bar;
To download the data from the SharePoint List, click on the Done button.
Now, in Excel we have the SharePoint data from the list.
On the right side we can see a settings pane.
In this pane we can:
- a – see the last update time and we can force a new data refresh;
- b – enable or disable the data downloading into the spreadsheet;
- c – load the data into PowerPivot.
Using the Load to Data Model option, the downloaded data is sent to PowerPivot and from there we can do some more advanced „stuff”, like:
- create relations between this table and another one in order to create a PivotTable from multiple sources;
- output a variety of visual data to your Excel worksheet.
- Chart and Table;
- Four Charts;
- Flattened PivotTable;
- use DAX (Data Analysis Expressions) to to create measures (an upgraded version of Excel formulas);
- publish dashboards to SharePoint;
At one of the above steps, Excel asked us to connect to the SharePoint site.
The POWER QUERY offers us an option that allows to change the account or to delete the stored connection data.
From the POWER QUERY tab click on Data Source Settings.
If we click on Edit Credential, for the selected site all we can do is to sign out and sign in with another account.
In order to delete a site and it’s stored credentials, click on the desired site and then click on the Delete option.
We have to confirm the deletion, by clicking on the Delete option.
Now, the selected site was deleted along with the credentials.