Power Automate: How to get the first level from a managed metadata (Term set) SharePoint field (hint: TaxonomyHiddenList)

If you need the TLDR; version: Add an extra step to do a “get item” from the hidden “TaxonomyHiddenList” and get the path there.

Or scroll down to the bottom to skip my adventures in finding out how to get it.

One of my customers had a specific requirement: They had a multilevel Managed Metadata field (you can go 7 levels deep on those) and they wanted to group their documents on the highest level. I knew I had to create a little workflow to grab the first level on the term set, but I had never expected it to be so ridiculously difficult. Dear Power Automate, I really want to love you, but you make it so damn hard..

Even after my first dabblings with Managed Metadata in Power Automate, where things went wrong as soon as I just added a document in a library (because the managed metadata field was empty and that causes Power Automate to crash).

For example, let’s take the good old country-city concept:
A term set called “geo” with following values:
– Belgium
– Belgium / Antwerp
– Belgium / Brussels
– France
-France / Marseille
– France / Paris

Our Managed Metadata structure

So when a user selects Antwerp as the location for some documents, the documents need to show up in the Belgium group.

By default, if you just group on the managed metadata field, you get something like this:

grouped by managed metadata field

This is ofcourse not what we want. We want both documents to be in the same group. So that isn’t possible in default SharePoint.

When I think Power Automate is too easy

I know what you think: Let’s just create a new calculated field called “Country” and do a supercool formula to just get all contents of the string before the “:”. For example:

=LEFT(text,FIND(:,Location)-1)

Unfortunately that doesn’t work as the managed metadata field can’t be used in a calculation.

So what is left is using a workflow. Every time I change the document, it needs to get the first level value and save it in a new field called “Country”. But what implementation of the field do we use?

WssID or TermGuid will probably not be what we need.. Let’s see what we can use!

Value doesn’t work

First try! Let’s try to use the Value. I mean.. that sounds easy right? Well.. almost too easy. When you try it out you get this for Country:

Value doesn’t work

Huh? What is that? We get our low-level item (which we selected) together with a bunch of characters… That doesn’t look like our value, right?

Label doesn’t work

Allright, label gets us somewhat closer. At least we get the item we selected.. But we only get the low level (which we selected). Even when we set the metadata field to show all levels (full path)

Label gets us a little closer

Ok, then the answer has to Path right? That sounds like the path of the item? It sounds like we would get the top level – low level item there!!

No.. Path doesn’t work

Hahaha, I had you there! No!! Path doesn’t work either! As a matter of a fact, when you look in your Flow run and you take a look at the item field data you see this:

Path is Null

Yes! Path is Null! Well ofcourse… that makes total sense!! When you look at the item, you see there is NO value that gets the full path of the term set item.

How to really get the first level data from a Managed Metadata field in Power Automate: Get it from the TaxonomyHiddenList

Ok, by now I can tell you how it does work! In short.. what you need to do is to poll a hidden list, called the “TaxonomyHiddenList” and there you will get the full path of the item. Never knew it would be so simple!! (I hope you can smell my sarcasm here).

I found this solution here. I could never come up with something like this. my Sherlock Holmes deduction skills are just not that good.

First step is to add a Get Item step to your flow. Point that to the SharePoint site, for the list name you need to fill in manually: TaxonomyHiddenList. It won’t show up in the List Name dropdown, because, as the name says… it is a hidden list. Duh.

You need to give the step an ID too, you can just use the ID of your field, the WssId.

Get item from the hidden list

When you then go as a next step to update your file properties, there are a ton of choices you get! When we take the “Path” field, look what happens!

it works!

Yes! It works! It grabs the full path!

Split your string to get the first part

The only thing we need to do now is to get the first part of the string. You can use this expression to get it:

first(split(outputs(‘Get_item_from_the_hidden_list’)?[‘body/Path’],’:’))

“Get Item from the hidden list” is the name of my step to get the … ah well, you get it. What it does is it splits the path string on the ‘:’ sign, and then takes the first part of it.

Pfew. That post came out a bit longer then it had to. As I said in the intro.. Power Automate, I really want to love you. I really do. Because I see so many awesome things. But you need to give more love to the Managed Metadata field, and SharePoint in general too. I mean, needing to reference a hidden “TaxonomyHiddenList” on my site is just too much..

About: Marijn

Marijn Somers (MVP) has over 14 years experience in the SharePoint world, starting out with SP2007. Over the years the focus has grown to Office 365, with a focus on collaboration and document management. He is a business consultant at Balestra and Principal Content Provider for "Mijn 365 Coach" that offers dutch employee video training. His main work tracks are around user adoption, training and coaching and governance. He is also not afraid to dig deeper in the technicalities with PowerShell, adaptive cards or custom formatting in lists and libraries. You can listen to him on the biweekly "Office 365 Distilled" podcast.

%d bloggers like this: