Connecting to Share Point Term Store

Hi Anonymous , 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.

Hi Anonymous ,

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.