Connecting to Share Point Term Store

[brennum]

Has anyone been able to connect Power BI to a Share Point Term Store group?  

[v-yajiewan-msft · Accepted solution]

Hi @brennum , rajendraongole1, thank you for your prompt reply !

We could also connect to the sharepoint term store via the Graph REST API.

Get data from the Blank Query, then click the advanced editor to type the M code(remember to replace the site url for yourself):

let
    // 1. Define the base URL
    baseURL = "https://yourtenant.sharepoint.com/sites/Test1/_api/v2.1/termstore/groups",

    // 2. Fetch all term groups (Groups)
    GetGroups = Json.Document(Web.Contents(baseURL)),
    GroupsTable = Table.FromRecords(GetGroups[value]),

    // 3. Define a function to fetch all term sets (Sets) within each term group
    GetSetsByGroup = (groupID as text) =>
        let
            setsURL = "https://yourtenant.sharepoint.com/sites/Test1/_api/v2.1/termstore/groups('" & groupID & "')/sets",
            setsResponse = Json.Document(Web.Contents(setsURL)),
            setsTable = Table.FromRecords(setsResponse[value])
        in
            setsTable,

    // 4. Add a column for term sets to each term group
    GroupsWithSets = Table.AddColumn(GroupsTable, "Sets", each GetSetsByGroup([id])),

    // 5. Define a function to fetch all terms within each term set
    GetTermsBySet = (groupID as text, setID as text) =>
        let
            termsURL = "https://yourtenant.sharepoint.com/sites/Test1/_api/v2.1/termstore/groups('" & groupID & "')/sets('" & setID & "')/terms",
            termsResponse = Json.Document(Web.Contents(termsURL)),
            termsTable = Table.FromRecords(termsResponse[value])
        in
            termsTable,

    // 6. Add a column for terms to each term set
    AddTermsToSets = Table.AddColumn(GroupsWithSets, "SetsWithTerms", 
        each Table.AddColumn([Sets], "Terms", (currentSet) => GetTermsBySet([id], currentSet[id]))),

    // 7. Expand the final results and add 'localizedNames.name'
    // First, expand 'SetsWithTerms' to get 'id', 'name', 'Terms', and 'localizedNames'
    ExpandedSets = Table.ExpandTableColumn(AddTermsToSets, "SetsWithTerms", {"id", "name", "Terms", "localizedNames"}, {"SetID", "SetName", "Terms", "LocalizedNames"}),

    // 8. Expand 'localizedNames' to get the 'name' field
    ExpandedLocalizedNames = Table.ExpandListColumn(ExpandedSets, "LocalizedNames"),
    ExpandedLocalizedNamesWithName = Table.ExpandRecordColumn(ExpandedLocalizedNames, "LocalizedNames", {"name"}, {"LocalizedSetName"}),

    // 9. Expand the 'Terms' column
    ExpandedTerms = Table.ExpandTableColumn(ExpandedLocalizedNamesWithName, "Terms", {"id", "labels"}, {"TermID", "Labels"}),
    #"Expanded Labels" = Table.ExpandListColumn(ExpandedTerms, "Labels"),
    #"Expanded Labels1" = Table.ExpandRecordColumn(#"Expanded Labels", "Labels", {"name", "isDefault", "languageTag"}, {"Labels.name", "Labels.isDefault", "Labels.languageTag"})

in
    #"Expanded Labels1"

Result for your reference:

Link for your reference:

List termStore groups - Microsoft Graph v1.0 | Microsoft Learn

Operations using SharePoint REST v2 (Microsoft Graph) endpoints | Microsoft Learn

Best regards,

Joyce

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

[v-yajiewan-msft · Accepted solution]

Hi @brennum , rajendraongole1, thank you for your prompt reply !

We could also connect to the sharepoint term store via the Graph REST API.

Get data from the Blank Query, then click the advanced editor to type the M code(remember to replace the site url for yourself):

let
    // 1. Define the base URL
    baseURL = "https://yourtenant.sharepoint.com/sites/Test1/_api/v2.1/termstore/groups",

    // 2. Fetch all term groups (Groups)
    GetGroups = Json.Document(Web.Contents(baseURL)),
    GroupsTable = Table.FromRecords(GetGroups[value]),

    // 3. Define a function to fetch all term sets (Sets) within each term group
    GetSetsByGroup = (groupID as text) =>
        let
            setsURL = "https://yourtenant.sharepoint.com/sites/Test1/_api/v2.1/termstore/groups('" & groupID & "')/sets",
            setsResponse = Json.Document(Web.Contents(setsURL)),
            setsTable = Table.FromRecords(setsResponse[value])
        in
            setsTable,

    // 4. Add a column for term sets to each term group
    GroupsWithSets = Table.AddColumn(GroupsTable, "Sets", each GetSetsByGroup([id])),

    // 5. Define a function to fetch all terms within each term set
    GetTermsBySet = (groupID as text, setID as text) =>
        let
            termsURL = "https://yourtenant.sharepoint.com/sites/Test1/_api/v2.1/termstore/groups('" & groupID & "')/sets('" & setID & "')/terms",
            termsResponse = Json.Document(Web.Contents(termsURL)),
            termsTable = Table.FromRecords(termsResponse[value])
        in
            termsTable,

    // 6. Add a column for terms to each term set
    AddTermsToSets = Table.AddColumn(GroupsWithSets, "SetsWithTerms", 
        each Table.AddColumn([Sets], "Terms", (currentSet) => GetTermsBySet([id], currentSet[id]))),

    // 7. Expand the final results and add 'localizedNames.name'
    // First, expand 'SetsWithTerms' to get 'id', 'name', 'Terms', and 'localizedNames'
    ExpandedSets = Table.ExpandTableColumn(AddTermsToSets, "SetsWithTerms", {"id", "name", "Terms", "localizedNames"}, {"SetID", "SetName", "Terms", "LocalizedNames"}),

    // 8. Expand 'localizedNames' to get the 'name' field
    ExpandedLocalizedNames = Table.ExpandListColumn(ExpandedSets, "LocalizedNames"),
    ExpandedLocalizedNamesWithName = Table.ExpandRecordColumn(ExpandedLocalizedNames, "LocalizedNames", {"name"}, {"LocalizedSetName"}),

    // 9. Expand the 'Terms' column
    ExpandedTerms = Table.ExpandTableColumn(ExpandedLocalizedNamesWithName, "Terms", {"id", "labels"}, {"TermID", "Labels"}),
    #"Expanded Labels" = Table.ExpandListColumn(ExpandedTerms, "Labels"),
    #"Expanded Labels1" = Table.ExpandRecordColumn(#"Expanded Labels", "Labels", {"name", "isDefault", "languageTag"}, {"Labels.name", "Labels.isDefault", "Labels.languageTag"})

in
    #"Expanded Labels1"

Result for your reference:

Link for your reference:

List termStore groups - Microsoft Graph v1.0 | Microsoft Learn

Operations using SharePoint REST v2 (Microsoft Graph) endpoints | Microsoft Learn

Best regards,

Joyce

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

[clausm73]

I have noticed that the method described doesn't handle pagination from the Graph API (actually is it the SharePoint REST API). Is that because the API's handling Term Stores are not paginated?

From reading API documentation I see OData mentioned several times - and that usually contains paginated results.

[brennum]

Thanks for the feedback.  It doesn't work for me .

I think my issue might be in my url for the term store.  It looks like this https://tenetid/sites/siteid/_layouts/15/site.aspx#/termstoretool

Bold text is redacted.    For other term store url's I have seen do not have an active server page (.aspx).   So I am wondering if I have the right url.

[v-yajiewan-msft · Accepted solution]

Hi @brennum ,

Are you using a Sharepoint server?

The code above works for Sharepoint Online, if you are using a local server you will need to use PowerShell + CSOM to get the data from the term store and then import it into Power BI for processing and presentation.

[brennum]

After further studying and understanding the proper URL, I am getting data.  We have a structure of Group==>Termset==>SubTerm==>Term that I am trying to build out.  Greatly appreciate your insight, expertise, and help with my issue.

[v-yajiewan-msft]

Hi @brennum,

If you find any answer is helpful to you, please remember to accept it.

It will help others who meet the similar question in this forum.

Thank you for your understanding.

[rajendraongole1]

Hi @brennum -Connecting Power BI to a SharePoint Term Store isn't a standard or straightforward feature, but it is possible to integrate SharePoint metadata, such as terms from a term store

You could refer to Using Power BI to Report on Managed Metadata Fields in SharePoint for details.

Create and manage terms in a term set - SharePoint in Microsoft 365 | Microsoft Learn

Power BI: How To Connect To SharePoint Online (youtube.com)

Step-by-Step Guide to Power BI and SharePoint Integration (analyticsinsight.net)

Hope this helps.