skip to Main Content

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.

1_Export2Excel

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.

2_EnableConnections

After allowing Excel to connect to SharePoint, all the data from list is downloaded into a spreadsheet.

3_ExcelList

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.

SP_List

Next, we must specify the URL to our SharePoint site.

5_SP_URL

One important step is about the authentication to the SharePoint site.
Here we can connect:

  • Anonymous;
  • Using Windows credentials;
  • Using MOS ID.

6_SignIn

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.

7_MOS_ID

In the bellow image we can see that the data is downloaded (101 KB when I took the screenshot).

8_MOS_SignIn_Another

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;
  • etc.

9_Query_Editor

Contextual Menu

Options

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.

Query_Settings

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.
    • PivotTable;
    •  PivotChart;
    • Chart and Table;
    • Four Charts;
    • Flattened PivotTable;
    • etc.
  • use DAX (Data Analysis Expressions) to to create measures (an upgraded version of Excel formulas);
  • publish dashboards to SharePoint;
  • etc.

11_PowerPivot

Account Information

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.

DS_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.

13_EditCredentials

In order to delete a site and it’s stored credentials, click on the desired site and then click on the Delete option.

14_DeleteData

We have to confirm the deletion, by clicking on the Delete option.

15_DeleteConfirmation

Now, the selected site was deleted along with the credentials.

16_NoData

(Visited 9 times, 1 visits today)

Cosmin Tătaru

Pasionat de blogging și tehnologie încă din 2009, Cosmin este aici să vă ajute să vă creșteți vânzările și productivitatea punând tehnologia la treabă pentru dumneavoastră.

Suport-Clienti
Mergeți sus