4/30/2019

SharePoint.Tables – Power BI – Power Query M Functions


The Microsoft DOCS article for SharePoint.Tables says "Returns a table containing a row for each List item found at the specified SharePoint list url." While this sounds like it returns list "Items", it actually returns a list of Lists. It also reads like the URL is supposed to point to a list. Instead, it should point to the site or subsite.
If this was a typical DOCS article, I'd click the Edit button and offer an update to the content. For some reason, there is no Edit button. So here is my proposed revision:

Syntax

SharePoint.Tables(**url** as text, optional **options** as nullable record) as table
 

About

Returns a table containing a row for each List item found at the specified SharePoint list site url. Each row contains properties of the a List. options may be specified to control the following options:
  • ApiVersion : A number (14 or 15) or the text "Auto" that specifies the SharePoint API version to use for this site. When not specified, API version 14 is used. When Auto is specified, the server version will be automatically discovered if possible, otherwise version defaults to 14. Non-English SharePoint sites require at least version 15.
 

Examples

Get a list of lists in a web:
let
    // Get a list of tables at the site URL... 
    SharePointSiteLists = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15])
in
    SharePointSiteLists

Get details about a list:
let
    // Get a list of tables at the site URL... 
    SharePointSiteLists = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    // Get a list named "Toys"...
    // (Could also use list GUID - [Id="a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5"] )
    ToyList = SharePointSiteLists{[Title="Toys"]}
in
    ToyList

Get a list of items in a list:
let
    // Get a list of tables at the site URL... 
    SharePointSiteLists = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    // Get a list named "Toys"...
    // (Could also use list GUID - [Id="a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5"] )
    ToyList = SharePointSiteLists{[Title="Toys"]},
    // Get the items from the list...
    Toys = ToyList[Items]
in
    Toys

 
Here's the output of the three examples above…

The list of lists:


 Data for a list:


List Items: (SharePoint tends to return a lot of extra columns you don't need.)

.

No comments:

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.