SharePoint.Files

D

Back to top

T

Back to top

#

Back to top

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

Overview of SharePoint.Files

SharePoint.Files is a Power Query M function that allows users to connect to SharePoint and retrieve files and folders from a SharePoint site. It is one of the many connectors available in Power Query, and it can be accessed via the “Get Data” button on the Power Query ribbon.

When the SharePoint.Files function is used, it prompts the user to enter the URL of the SharePoint site they want to connect to, as well as the credentials they want to use to authenticate to the site. Once connected, the user can then select the files and folders they want to retrieve from the site.

The M Code Behind SharePoint.Files

Behind the scenes, the SharePoint.Files function is powered by M code. M is a functional programming language used by Power Query to define data transformations. When the SharePoint.Files function is used, Power Query generates M code that connects to SharePoint and retrieves the desired files and folders.

Here is an example of the M code that is generated when the SharePoint.Files function is used:

let

Source = SharePoint.Files(“https://contoso.sharepoint.com/sites/marketing”, [ApiVersion = 15]),

#”Filtered Rows” = Table.SelectRows(Source, each ([Folder Path] = “/Shared Documents”)),

#”Removed Other Columns” = Table.SelectColumns(#”Filtered Rows”,{“Name”, “Content”}),

#”Sorted Rows” = Table.Sort(#”Removed Other Columns”,{{“Name”, Order.Ascending}})

in

#”Sorted Rows”

Let's break down what is happening in this code:

- The "let" statement defines the beginning of the query. It is used to define variables that can be referenced later in the code.

- The "Source" variable is defined using the SharePoint.Files function. The URL of the SharePoint site is specified, as well as the API version to use (in this case, version 15).

- The "Filtered Rows" variable is defined using the Table.SelectRows function. This function filters the "Source" table to only include rows where the "Folder Path" column is equal to "/Shared Documents".

- The "Removed Other Columns" variable is defined using the Table.SelectColumns function. This function removes all columns from the "Filtered Rows" table except for the "Name" and "Content" columns.

- The "Sorted Rows" variable is defined using the Table.Sort function. This function sorts the "Removed Other Columns" table by the "Name" column in ascending order.

- Finally, the entire query is returned as the output of the function.

Customizing the M Code

One of the great things about Power Query is that users can customize the M code behind the functions to suit their needs. For example, users can modify the SharePoint.Files function to retrieve files and folders from different locations within a SharePoint site, or to retrieve different types of files.

To customize the M code behind the SharePoint.Files function, users can click the "Advanced Editor" button on the Power Query ribbon. This will display the M code for the query, which users can then modify as needed.

The SharePoint.Files function is a powerful tool for retrieving files and folders from a SharePoint site using Power Query. Behind the scenes, the function is powered by M code, which can be customized to suit the needs of the user. By understanding the M code behind the SharePoint.Files function, users can gain a deeper understanding of how Power Query works and how to customize it to meet their needs.

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)