SharePoint folder
Power Query is a powerful tool that can be used to connect, transform and load data from various sources into Power BI. One of the most common sources is SharePoint folders. In this article, we will discuss the Power Query M Language code for connecting to the SharePoint folder data source from inside Power BI.
Overview of SharePoint folder Data Source
SharePoint folder data source allows users to access files and folders from a SharePoint site. To access this data source from Power BI, we need to provide the SharePoint site URL and the folder path. Power Query supports two modes of authentication for connecting to SharePoint: Windows and Anonymous. Windows authentication requires users to enter their SharePoint username and password, while Anonymous authentication allows users to access SharePoint without any authentication.
Connecting to SharePoint folder Data Source using Power Query M Language Code
Connecting to SharePoint folder data source from Power BI involves writing M Language code. M Language is the functional programming language used by Power Query to transform data.
The following is the M Language code for connecting to SharePoint folder data source:
let
Source = SharePoint.Files(“https://sharepointsiteurl/”, [ApiVersion = 15]),
FolderPath = Source{[#”Folder Path”=”folderpath”]}[Content],
Files = FolderPath{[#”Folder Path”=”folderpath”, #”Name”=”filename.csv”]}[Content]
in
Files
To connect to SharePoint folder data source, we first need to use the `SharePoint.Files` function. This function takes two arguments: the SharePoint site URL and the API version. The API version is optional and defaults to 14. In our example, we have specified the API version as 15.
After connecting to the SharePoint site, we can access the folders and files using the `Content` property. The `Content` property returns a table that contains all the files and folders in the SharePoint site.
To access a specific folder, we need to filter the table using the folder path. In our example, we have used the `FolderPath` variable to filter the table for the specified folder path.
To access a specific file, we need to filter the table using both the folder path and the file name. In our example, we have used the `Files` variable to filter the table for the specified folder path and file name.
Using Windows Authentication to Connect to SharePoint
To connect to SharePoint using Windows authentication, we need to modify the M Language code. The following is the modified M Language code:
let
Source = SharePoint.Files(“https://sharepointsiteurl/”, [ApiVersion = 15, WindowsCredential = [Credential=[Username=”username”,Password=”password”]]]),
FolderPath = Source{[#”Folder Path”=”folderpath”]}[Content],
Files = FolderPath{[#”Folder Path”=”folderpath”, #”Name”=”filename.csv”]}[Content]
in
Files
In the modified code, we have added the `WindowsCredential` parameter to the `SharePoint.Files` function. This parameter takes a record that contains the username and password for the SharePoint site.
Using Anonymous Authentication to Connect to SharePoint
To connect to SharePoint using Anonymous authentication, we need to modify the M Language code. The following is the modified M Language code:
let
Source = SharePoint.Files(“https://sharepointsiteurl/”, [ApiVersion = 15, Anonymous = true]),
FolderPath = Source{[#”Folder Path”=”folderpath”]}[Content],
Files = FolderPath{[#”Folder Path”=”folderpath”, #”Name”=”filename.csv”]}[Content]
in
Files
In the modified code, we have added the `Anonymous` parameter to the `SharePoint.Files` function. This parameter is set to `true` to enable Anonymous authentication.
Conclusion
Power Query M Language code provides a powerful way to connect to SharePoint folder data source from inside Power BI. By using M Language, we can customize the connection to meet our specific requirements. We hope this article has provided you with the necessary information to connect to SharePoint folder data source using Power Query M Language code.
