Connecting to SharePoint Folders

Connecting to SharePoint Folders is similar to connecting to folders on your file system. The Get Data > Folder connector allows you to connect to a folder containing files of the same type and combine the data they contain into a single query. You simply specify the folder you want to connect to and all of the data in all of the files in that folder and any sub-folders can be accessed.

Power BI also allows you to perform a similar operation on SharePoint folder. However, the SharePoint folder connector works slightly differently.

The Get Data > SharePoint Folder command actually connects to a SharePoint site rather than a SharePoint folder. Thus, when you use the command, you will notice that you are asked to specify the URL of a SharePoint site, rather than that of a SharePoint folder.

You then need to supply the “Microsoft account” credentials required for accessing the specified site.

The Preview window then appears, showing a list of all the files found anywhere in the specified site, including all document libraries and folders.

This normally includes files of different types. So, instead of attempting to combine files at this stage, it normally makes more sense to click Transform data.

The resulting query then shows metadata about the files located anywhere within the SharePoint site; i.e., on the root or in any document library or folder. The folder path column shows the URLs of the container of each file; and it is normally one of these items that you want to connect to.

To target a particular folder, we can now simply apply a filter to the Folder Path column of the metadata table.

Having applied a filter to limit the file to those contained in the desired folder, to manually combine the files listed, click the Combine Files button on the right of the “Content” column.

This displays the Combine File dialog and from here everything functions in the same way as when using Get Data > Folder. The Combine Files window allows you to specify a sample file. The sample file determines the columns which will be included in the output file and the data connector which will be used, for example Text/CSV. (If no sample file is specified, by default, the first file in the folder is used as the sample file.)

The output query produced by the Get Data > SharePoint Folder operation relies on a number of helper queries. These queries are required by the output query and therefore cannot be deleted.

The helper file called “Transform Sample File” is used to determine the connection and transformation which will be carried out on each file in the folder. Any additional transformations carried out on this query will also be applied to all files.