Using Power Query Excel to Pull SharePoint Documents: Library Managed Metadata

Introduction

This article explains how to access and work with SharePoint document library columns, specifically library managed metadata, using Power Query in Excel. By the end of this article, you will understand the key concepts and have a step-by-step guide to implementing this process.

Prerequisites

To follow this article, you need:

• Access to a SharePoint document library with managed metadata.
• Excel 2016 or later with Power Query add-on installed.

Accessing SharePoint Document Library Columns: Library Managed Metadata

First, let's establish a connection to the SharePoint document library using Power Query.

Step 1: Connect to SharePoint

1. Open Excel and click on the "Data" tab in the ribbon.

2. In the "Get & Transform Data" group, click on "From Other Sources" and then "From SharePoint".

3. Enter the URL of your SharePoint site and the library path in the "Site Address" and "List/Library" fields, respectively.

4. Click "OK" to establish the connection.

Step 2: Navigate the SharePoint Data

1. In the Power Query Editor, expand the "Tables" tab and select the table representing your document library.

2. Expand the "Columns" tab and check if the library managed metadata columns are present.

Loading Library Managed Metadata as Separate Columns

If the library managed metadata is not loaded as separate columns, we can load it using the "Add Column" tab in Power Query.

Step 3: Load Library Managed Metadata as Separate Columns

1. In the Power Query Editor, click on the "Add Column" tab and select "Custom Column".

2. In the "Custom Column" formula bar, enter the following formula:

Replace "CustomColumnName" with the desired name for the new column and "ManagedMetadataColumnName" with the name of the library managed metadata column.

3. Click "OK" to apply the transformation.

Filtering and Transforming Data

Now that we have loaded the library managed metadata as separate columns, we can filter and transform the data as needed.

Step 4: Filter and Transform Data

1. Apply any necessary filters to the data using the "View" tab in Power Query.

2. Transform the data using the "Add Column", "Remove Columns", and "Transform" tabs as needed.

Loading Data Back into Excel

Once you've finished filtering and transforming the data, load it back into Excel by clicking on the "Home" tab and selecting "Close & Load".

References

For further reading, check out these resources:

• Power Query Overview of Data Sources
• Connect to SharePoint