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

SharePoint Search Weirdness – Part 5: Search REST API Ignores Duplicates

A continuation of the "Search Weirdness" series!

If you are a developer, or a SharePoint 2013 workflow designer, then you probably have used the SharePoint Search REST API. Did you know that you are probably not getting all of the results expected?

Here’s a typical REST search for the word “sharepoint”:

http://yourSiteUrl/_api/search/query?querytext='sharepoint'

Or if you would like to be a little more selective:

http://yourSiteUrl/_api/search/query?querytext='sharepoint'&selectproperties='Title,Author'&refinementfilters='fileExtension:equals("docx")'

or you would like to return more than the default number of items:

http://yourSiteUrl/_api/search/query?querytext='sharepoint'&selectproperties='Title,Author'&refinementfilters='fileExtension:equals("docx")'&rowlimit=1000

The problem with the above searches is that Search thinks some of your results are duplicates, so it removed them! To solve this problem just add this to your URL:

    &trimduplicates=true

Your search URLs then might look like these:

http://yourSiteUrl/_api/search/query?querytext='sharepoint'&trimduplicates=true

http://yourSiteUrl/_api/search/query?querytext='sharepoint'&selectproperties='Title,Author'&refinementfilters='fileExtension:equals("docx")'&trimduplicates=true

http://yourSiteUrl/_api/search/query?querytext='sharepoint'&selectproperties='Title,Author'&refinementfilters='fileExtension:equals("docx")'&rowlimit=1000&trimduplicates=true

.

A SharePoint REST API Tester with an AJAX and Workflow Writer

 

A JavaScript project to use with a Content Editor Web Part to test SharePoint REST API calls, and create AJAX sample code and SharePoint Designer 2013 Workflow steps. It includes over 40 ready to test samples to query SharePoint and to create and delete items, folders, lists and sites.

While learning the SharePoint REST API, I created a little REST tester Content Editor Web Part. Later when I explored SharePoint 2013 Workflow REST calls I expanded the tool to include step by step instructions to add the calls to a workflow. After presenting this at the Cincinnati SharePoint User Group and at the Nashville SharePoint Saturday I decided to take the time to clean it up a bit and share it here.

What you will need:

• A SharePoint 2013 or later site.
• A web part page with a Content Editor Web Part.
• The JavaScript file. (has a .html extension so you can edit it in SPD.)
• Permissions to access the content you want to access using REST!
• Some documentation:
  • A REST Syntax wall poster: http://www.microsoft.com/en-us/download/details.aspx?id=41147
  • REST API reference and samples: https://msdn.microsoft.com/en-us/library/office/jj860569.aspx

You can also download the file from the GitHub project.

---

This is the main screen.

This is partial list of the sample REST calls. A more complete list is at the end of this article, and I’ll be adding more over time.

 

The in the page test of a REST call.

 

The generated AJAX Code Sample

 

The SharePoint 2013 Workflow Steps for the Web Service Call

 

Steps to install to your SharePoint Site

1. If your master page is not already loading jQuery, download jQuery (just about any version) and upload to the Site Pages library. 
2. Download the SharePointRESTtester.html file to your PC. 
3. Edit the file and update the line that loads jQuery to point your jQuery file or CDN.
4. If your master page already loads jQuery, then delete the <script> block that loads the jQuery file.(the first line of the file)
5. Upload the SharePointRESTtester.html file to your Site Pages library. (Copy the URL to the file.)
6. Add a Web Part Page to your project:
   1. In the Site Pages library, click the FILES ribbon, click New Document and click Web Part Page.
   2. Enter a page name like "SharePointRESTtester". 
   3. From the library dropdown select Site Pages. 
   4. Click Create.
7. Click Add a Web Part. 
8. Add a Content Editor Web Part.
9. Click the web part's dropdown and click Edit Web Part.
10. Enter or paste the path to the SharePointRESTtester.html file.
11. Click OK and then in the ribbon click Stop Editing.
12. You should now see the tester. Click the dropdown and you should see data in the boxes. If not, then the jQuery library did not get loaded.
13. Add to your Quick Launch or your Follow list!

 

To use the tester…

1. Select a sample from the dropdown, or enter your own URL, Method, Header JSON and if needed, the Body JSON.
2. Find the Do It! button. The first check box will actually run the code. *** Warning Will Robinson, stuff could get added, changed or deleted! ***
3. The second and third checkboxes simple hide or show the JavaScript Ajax code and the SharePoint 2013 workflow steps.

 

SharePoint REST Examples for Queries

• Get information about the current site collection.
• Get information about the current web.
• Get the Regional Settings for the current web.
• Get the Time Zone for the current web.
• Get SharePoint's list of Time Zones.
• Get a list of all webs below the current web.
• Get the primary site collection administrator (Owner).
• Get the primary site collection Secondary Contact.
• Get a web's LastItemModifiedDate
• Get a list of lists from the current web. (all data)
• Get a list of lists from the current web. (Just the title)
• Get a count of items in a library.
• Get a count of items in a library. (Option #2)
• Get all items in a list/library.
• Get all items in a library with filename and path.
• Get a list folder's properties.
• Get a count of items in a list folder.
• Get all items in a list/library filtered by date.
• Get all items in web level recycle bin.
• Get selected properties of all items in web level recycle bin.
• Get all items in a list/library filtered by a range of dates.
• Search
• People Search

SharePoint REST Examples for Lists

• Create a new list
• Add a new item to a list
• Add a new folder to a list
• Delete an item from a list using ID
• Delete an item, to the Recycle Bin, from a list using ID
• Update an item using ID
• Delete a list
• Delete a list to the Recycle Bin

SharePoint REST Examples for Sites

• Create a new subsite.
• Delete a site (Warning Will Robinson! Does not go to the Recycle Bin!)

SharePoint REST Examples for User Profiles

• Get User Profile info about the current user.
• Get all User Profile properties for a user.
• Get User Profile info about a user's manager.

SharePoint REST Examples for Permissions

• Get a list of Role Definitions for a site.
• Get a list of Site Users. The ID is useful when setting permissions.
• Get a list of Site Groups. The ID is useful when setting permissions.
• Get a list of Site Groups by name.
• Get a list of Site Groups where name contains 'string'.
• Break inheritance on a subsite.
• Break inheritance on a list.
• Break inheritance on a list item.
• Grant permissions (Role Assignment) on a list.
• Remove permissions (Role Assignment) on a list.

SharePoint REST Examples for Filter Select and OrderBy

• Get a list of Site Users who are not Site Collection admins. Get selected fields and sort.

SharePoint REST Examples for SharePoint 2010 style REST - _vti_bin/ListData.svc

• Get a list of lists and libraries (EntitySets).
• Find list items greater than a date.
• Find list items between two dates.

 

.