Filter Term Store / Managed Metadata / Taxonomy in Power Automate

 Posted on February 9, 2024

Learn about the Term store, and how to filter a Term store column in Power Automate

What is the Term Store?

The Term store (also known as Managed Metadata or Taxonomy) is what I would call an advanced choice list. The benefits is that you can provide choices in a treeview style. Another benefit is that the Term store can be accessed in your entire tenant (if you have a choice field created directly on your list, or as a content type, in can only be accessed on that specific site)

The Term store has many names

Term store SharePoint admin center showing the Term store navigation label

Taxonomy SharePoint column settings showing the Taxonomy field type label

Managed metadata SharePoint column settings showing the Managed metadata field type label

If you have not worked with the Term store before you can find it in the admin center

https://tenant-admin.sharepoint.com/_layouts/15/online/AdminHome.aspx#/termStoreAdminCenter

Working with the term store

Click on the 3 dots and Add term group

SharePoint Term store admin center with the three-dot context menu open and Add term group option highlighted

Error message
The first time I worked with the Term store in my tenant I got an error saying An unexpected error has occurred. Please try again later. - I thought that there might be some issues with Microsoft, until I found out that the error should say You are not a term store manager

Term store error message saying An unexpected error has occurred caused by missing term store manager permissions

Add yourself as an admin, and add a new term group again, and instead of an error message, your should get a success message

Term store admin center showing a success message after successfully adding a new term group

Start building your Term store in a tree structure - notice that the first name (the folder icon) is mainly for admin purpose in the Term store, or when adding your Term store/Managed metadata column.

In my example below, the user will only see Animals and the available options - so add one more level then what feels natural.

Term store tree structure with an Animals group showing nested terms that users can select

Don’t create your entire Term store set in one sitting, because you can’t drag and drop the options around.

Create the structure you want with 1 or 2 options, and then create the Managed metadata column in your list, and check out how it looks.

The Term store has options to copy, move, merge etc., but I think it depends on the situation - but don’t be afraid to fool around

Term store context menu showing options to copy, move, merge, and reuse terms

What the users see
I have created a new list where people can choose their favorite animal

SharePoint list with a Managed metadata column where users can choose their favorite animal

With Managed metadata, the users have a tree like experience - and the terms can be reused on all lists in the tenant

SharePoint Managed metadata field picker showing the tree-view selection experience for choosing an animal term

Filtering term store column in Power Automate

If we want the get a list of all the items with cats the natural thing would be to use the ODATA filter, and use internal name eq cats

internalname eq 'Cats' Animals eq 'Cats'

Power Automate Get items action with a failing ODATA filter attempting to filter directly on the Animals taxonomy column

But if we do that, then we get the following error

The field ‘Animals’ of type ‘TaxonomyFieldType’ cannot be used in the query filter expression.

Well that means it can’t be done? Or can it?

If we don’t care about any specific column (which I think is OK in most cases) we can use this expression in our ODATA filter (TaxCatchAll/Term)

TaxCatchAll/Term eq 'Cats'

Power Automate Get items action using TaxCatchAll/Term eq Cats as the ODATA filter to retrieve items with the Cats term

This will return all items where Cats is selected (notice we did not write a specific column name, so it returns items from all columns, so if anyone had selected Cats in Animals or Animals2 that item would be returned)

We can take it even further
There is a hidden list named TaxonomyHiddenList with all of the information available in the Term store. We can get it by using the Get items action and in the List Name option, select Enter custom value and insert TaxonomyHiddenList

Note: The list is very big, so you should filter down the term you want. Here we are looking for anything with where Title eq Cats

Power Automate Get items action querying the hidden TaxonomyHiddenList filtered by Title eq Cats to retrieve the term ID

Let’s take the first term ID that is available in the filtered list, by using this expression and insert it into a variable

outputs('Get_items_TaxonomyHiddenList')?['body/value'][0]?['IdForTerm']

Then we could filter our first Get items with the filter array

Power Automate Filter array action using the term ID variable to filter items matching the Cats taxonomy term

Single item

If you have a single item (Action: Get item) from a list - and would like to filter another list with that specific Term store ID - you can use TaxCatchAll/IdForTerm

TaxCatchAll/IdForTerm eq 'your-item-name/TermGuid'

Power Automate Get items action using TaxCatchAll/IdForTerm to filter a list by a specific taxonomy term GUID from a single item

Now you know how to work with the Term store, and how to filter in Power Automate.

There are other ways to work with the termstore, see my other post:
SharePoint-PowerAutomate-filter-termstore

‎


SharePoint  Power Automate  Beginner 

See also