Pulling Data from Custom Properties in the SharePoint Term Store to Power BI

[RJH]

Hi everyone,

I have a SharePoint site with data stored in Term Sets in the Term Store linked to Managed Metadata fields in lists.  I'm using functionality in the Custom Properties tab to store some additional data about Terms.  

I'm able to pull Term Store data across to Power BI - it's found in the TaxonomyHiddenList when I connect to my SharePoint Online site - but I can't find this Custom Properties data anywhere.

Does anyone know how I can make this data available in Power BI?

Thanks!

Richard

[TottoPro]

I spotted your question while searching for a solution getting these properties in a SharePoint WebPart.

Following the Microsoft documentation for the SharePoint Rest API ( https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint... ) in some cases "[...] these properties are not included in the default query, and must be explicitly requested".

For my SharePoint situation i was successfull with this url: https://[tenant-name].sharepoint.com/sites/[sitename]/_api/v2.1/termStore/termSets/[setId]/terms/[te...

Maybe this helps you finding your solution.

[diegopach]

Hello, have you found any alternative to this problem? We have it too.
I think as a workaround, what we see viable is to have a list/table that interfaces. Through SPFx populate the table. Then, a good synchronization methodology. Finally PowerBI connects to that table/list. It's a long way to go, but it's the only thing I can think of at the moment.

Best regards!

[Daryl-Lynch-Bzy]

Hi @RJH, did you find a solution to this issue?  I wanted to get the "Synomyns" and "Description" from the Term Store for the Tag used in SharePoint lists.  The "TaxonomyHiddenList" does not include this additional detail.

[RJH]

Hi @Daryl-Lynch-Bzy ,

I never did find a solution to this.  As far as I could tell there was no way with existing functionality (at the time) to pull that data into lists.  I never received any follow up from Zoe or anyone else at Microsoft unfortunately, so I assume they didn't find anything either.

Richard

[dax]

Hi RJH,

You siad you add this in List, could you get this from sharepoint? I am not familiar with this part and will ask other engineer to help me test this in my environment, I will inform you as soon as I get it. You could refer to Using Power BI to Report on Managed Metadata Fields in SharePoint for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RJH]

Hi Zoe,

Not sure what you mean by 'can you get it from SharePoint'?  I have Term Set data referenced in SharePoint lists by way of Managed Metadata fields, but this Custom Properties data is actually still not available to be viewed in SharePoint lists.  I think it's a bit of functionality that could probably use some improvement from Microsoft.  When I pull List data from SharePoint using PowerBI, this Custom Properties data is not available there either - perhaps that's what you mean.  

Thanks for the link.  I had seen that post previously - I even posted this same question in the comments of that blog!  It was very helpful, and I actually used that as a guide in pulling Term Store data to SharePoint, but in following those steps I still was not able to see any data from the Custom Properties tab of the Term Store in Power BI.

Look forward to hearing the results of your testing!

Richard