Three Ways to Load SharePoint Data into Power BI and Excel

Power BI includes a connector/wizard for SharePoint lists that makes is easy to load list content. It works great, but has a shortcoming or two. It first retrieves a list of all of the lists, and then it retrieves all of the list content… even if you only need a small subset of the data.

In this little article I'll show these approaches:

• Using the Wizards
• Writing your own "M" formulas
• Using the SharePoint ODATA RESTful web services, for lists and a lot more!

While they work for Power BI, they also work for Excel Power Query.

Using the Wizards

The wizards will write "M" formulas for you to connect to the SharePoint list. Although this will let you transform the data any way you like, all of the list content will be downloaded into Power Bi and then filtered.

1. Launch Power BI Desktop
2. Click Get Data from the ribbon
3. If the full list of connectors is not displayed, click More.
4. Scroll, or use the search box, to find and click the “SharePoint Online List” or "SharePoint List" connector.
5. Enter the Site URL. (Just the URL to the site, not to a page or a list.)
   Example: https://yourDomain.sharepoint.com/sites/yourSite or https://yourDomain.sharepoint.com/sites/yourSite/yourSubSite
6. Click OK.
7. Find and checkmark your SharePoint list.
8. Review your data and click Edit. (SharePoint adds a number of hidden/internal columns that you will probably want to exclude.)
9. Optional: In the Query Settings area, click in the Name box and enter a new name for the imported data.
10. Select all of the columns that you do not need and click Remove Columns. Or, select the columns you want to keep, click the dropdown under Remove Columns, and click Remove Other Columns.
11. Click Close & Apply.
12. Create your visuals!

Writing your own "M" formulas

The wizards just write formulas for you. With a little practice, you can write better ones! Although this will let you transform the data any way you like, all of the list content will still be downloaded into Power Bi and then filtered. (i.e. keep reading to see how to use the REST web services to download only what you need.)

1. Launch Power BI Desktop
2. Click Get Data from the ribbon
3. Click Blank Query. This will open the Query Editor.
4. Click the Advanced Editor button.
5. Write your formula, save your changes.
6. You can continue customizing the data in the Query Editor.
7. Click Close & Apply.
8. Create your visuals!

In the blank query you will see something like this:

let
    Source = ""
in
    Source

When you run the wizard in the previous example, it writes a formula similar to this:

let
    Source = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    #"a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5" = Source{[Id="a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5",{{"ID", "ID.1"}})
in
    #"Renamed Columns"

You can write a better formula than the wizard!

The "let" selects and restructures the data while the "in" is the final result where we get the data for the model. The "let" contains a series of functions that drill down, layer by layer, to get to the data you need.

The "Source" is a variable and can have any name. This first line selects the data source, SQL, SharePoint, etc. While the documentation for SharePoint.Tables says it uses a URL to the list, it is actually the URL to the server. (Kind of like how a SQL connection string names a database, but not a table.)

Here's an example of a hand written "let" statement that uses friendly names instead of GUIDs for tables and obvious names for the variables:

let
    SharePointSite = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    ToyList = SharePointSite{[Title="Toys"]},
    Toys = ToyList[Items]
in
    Toys

These functions can be chained, so if you prefer a one line version:

let
    Toys = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]){[Title="Toys"]}[Items]
in
    Toys

And if you want to pick just the columns you need without a lot of clicking…

let
    Toys = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]){[Title="Toys"]}[Items],
    ToysData = Table.SelectColumns(Toys,{"Id", "Title", "Price", "Modified"})
 in
    ToysData

More on M:
    https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference

Ok, that's good to know… but we still bring back all of the rows of data, even if we just want a few.

Using the SharePoint ODATA RESTful Web Services

The SharePoint List connector lets you bring in data from SharePoint lists and libraries. Using the SharePoint REST API web services you can bring in just about any kind of data found in SharePoint, including list data, lists of lists, lists of users, lists of sites and much more. Even better, you can write filters and select columns so only the needed data is sent from SharePoint to Power BI. (And with other REST APIs you can bring in just about any data from Office 365!)

1. Launch Power BI Desktop
2. Click Get Data from the ribbon
3. Click OData Feed.
4. Enter a URL to a SharePoint web service. This one retrieves all Touring bikes.
   This example assumes a list named “Bikes” where we only want three columns and only the rows for "Touring" bikes.
   Example:
   https://yourServer/sites/yourSite/_api/Lists/GetByTitle('Bikes')/Items?$select=price,color,style&$filter=category eq ' Touring'
5. Click OK.
6. Enter credentials, if needed.
7. Click OK.  (You have already selected your columns and rows from the REST query, so no additional Power Query edits needed.)
8. Create your visuals!

Note: You could start with a Blank Query and enter something like this:

let
    Source = OData.Feed("https://yourDomain.sharepoint.com/sites/yourSite/_api/web/lists/getbytitle('Bikes')/Items?$select=Title,Bike_x0020_Type,Size,Retail", null, [Implementation="2.0"])
in
    Source

There are REST Web Services for Everything!

A few other ideas: (and even more ideas here: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/get-to-know-the-sharepoint-rest-service)

• Get a list of all of the lists in a site:
  https://yourServer/sites/yourSite/_api/web/lists
• Get a list of all items in a site’s recycle bin:
  https://yourServer/sites/yourSite/_api/web/Recyclebin
• Get a list of all subsites in the current Site Collection:
  https://yourServer/sites/yourSite/_api/web/webs
• Get a list of all site users:
  https://yourServer/sites/yourSite/_api/web/siteusers?$filter=startswith(LoginName,'i:0%23.f')

You can load multiple REST queries, and then define relationships to build reports for all kinds of SharePoint activity!

 

Learning the SharePoint REST API

To experiment with SharePoint REST services you can add my SharePoint REST Tester to your SharePoint site. See details here:

   https://techtrainingnotes.blogspot.com/2017/04/a-sharepoint-rest-api-tester-with-ajax.html

and download from here:

   https://github.com/microsmith/SharePointRESTtester