SharePoint.Tables

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function SharePoint.Tables

Understanding the SharePoint.Tables Function

SharePoint is a popular collaboration platform used by many organizations to store and manage documents and data. Power Query provides a SharePoint.Tables function that allows users to connect to SharePoint lists and libraries and retrieve data.

The SharePoint.Tables function takes in two arguments: URL and options. The URL argument specifies the URL of the SharePoint site or subsite to connect to, while the options argument specifies additional options such as the name of the SharePoint list or library to retrieve data from.

Here’s an example of the SharePoint.Tables function in action:

let

Source = SharePoint.Tables(“https://mysharepointsite.sharepoint.com/sites/MySite”, [ApiVersion = 15]),

#”My List” = Source{[Name=”My List”]}[Items]

in

#”My List”

In this example, we're connecting to a SharePoint site called "MySite" and retrieving data from a list called "My List".

The M Code Behind SharePoint.Tables

Behind the scenes, the SharePoint.Tables function uses M code to connect to the SharePoint site and retrieve data. Let's take a closer look at the M code behind the function.

First, the function uses the SharePoint.Contents function to retrieve the contents of the SharePoint site:

let

Source = SharePoint.Contents(“https://mysharepointsite.sharepoint.com/sites/MySite”, [ApiVersion = 15])

in

Source

This code snippet retrieves the contents of the SharePoint site at the specified URL. The ApiVersion option specifies the version of the SharePoint API to use.

Next, the function uses the Table.SelectRows function to filter the contents of the SharePoint site and retrieve the specified list or library:

let

Source = SharePoint.Contents(“https://mysharepointsite.sharepoint.com/sites/MySite”, [ApiVersion = 15]),

#”Filtered Rows” = Table.SelectRows(Source, each ([Kind] = “List” and [Name] = “My List”)),

#”Expanded Contents” = Table.ExpandTableColumn(#”Filtered Rows”, “Contents”, {“Name”, “Properties”, “Value”}, {“Name”, “Properties”, “Value”}),

#”Filtered Rows1″ = Table.SelectRows(#”Expanded Contents”, each ([Name] = “Value”)),

#”Removed Other Columns” = Table.SelectColumns(#”Filtered Rows1″,{“Value”}),

#”Expanded Value” = Table.ExpandTableColumn(#”Removed Other Columns”, “Value”, {“odata.type”, “odata.id”, “odata.editLink”, “ID”, “Title”, “Name”, “Description”, “BaseType”, “ItemCount”, “RelativeUrl”, “ServerRelativeUrl”, “Created”, “Modified”, “LastItemModifiedDate”, “Id”, “ContentTypeOrder”, “ParentWebUrl”}, {“odata.type”, “odata.id”, “odata.editLink”, “ID”, “Title”, “Name”, “Description”, “BaseType”, “ItemCount”, “RelativeUrl”, “ServerRelativeUrl”, “Created”, “Modified”, “LastItemModifiedDate”, “Id”, “ContentTypeOrder”, “ParentWebUrl”}),

#”Added Custom” = Table.AddColumn(#”Expanded Value”, “Items”, each SharePoint.Tables(“https://mysharepointsite.sharepoint.com/sites/MySite”, [ApiVersion = 15, FolderPath = [RelativeUrl] & “/Items”])),

#”Removed Other Columns1″ = Table.SelectColumns(#”Added Custom”,{“Items”})

in

#”Removed Other Columns1″

This code snippet filters the contents of the SharePoint site to retrieve the specified list or library. The Table.ExpandTableColumn function is used to expand the contents of the list or library. The function then uses the SharePoint.Tables function with the FolderPath option to retrieve the items within the list or library.

In this article, we've explored the M code behind the Power Query M function SharePoint.Tables. We've learned that the function uses M code to connect to the SharePoint site and retrieve data from a specified list or library. By understanding the M code behind the function, we can better customize our SharePoint data retrieval in Power Query.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)