Using Microsoft Graph API to Read Microsoft Purview Data

Malvine Owuor

April 2nd, 2025

2579 Views

Microsoft Purview is a powerful data governance solution that helps organizations discover, classify, and manage data across their entire data estate. However, accessing Purview metadata directly from Power BI or Excel might seem tricky.

Good news: You can easily leverage the Microsoft Graph API + Power Query (M Language) to read Purview metadata!

✅ What You'll Learn

✔️ Register an Azure AD App to access Purview via Graph
✔️ Grant the necessary permissions
✔️ Generate an access token in Power Query
✔️ Query Purview using Microsoft Graph API from Power Query
✔️ Load Purview data into Power BI or Excel

🟣 Step 1: Register an App in Azure AD

✔️ Go to Azure Portal > Azure Active Directory > App registrations > New registration
✔️ Name it: PurviewGraphReader
✔️ Set Redirect URI as https://localhost (for testing)
✔️ Click Register

🟣 Step 2: Assign API Permissions

✔️ Go to API permissions > Add a permission
✔️ Choose Microsoft Graph
✔️ Select Application permissions
✔️ Search for and add: Catalog.Read.All (needed for Purview Catalog API)
✔️ Click Grant admin consent

🟣 Step 3: Create a Client Secret

✔️ Go to Certificates & Secrets > New client secret
✔️ Provide a description and set expiry
✔️ Save the generated secret value securely

🟣 Step 4: Collect Important Details

✔️ Directory (Tenant) ID
✔️ Application (Client) ID
✔️ Client Secret
✔️ Purview Account Name & Endpoint (e.g., https://your-account.purview.azure.com)

🟣 Step 5: Sample Power Query Code

let
    // Parameters - replace with your values
    tenantId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
    clientId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
    clientSecret = "YOUR_CLIENT_SECRET",
    purviewAccountName = "your-purview-account",
    
    // Get Token URL
    tokenUrl = "https://login.microsoftonline.com/" & tenantId & "/oauth2/v2.0/token",

    // Request Access Token
    tokenResponse = Json.Document(Web.Contents(tokenUrl,
        [
            Content = Text.ToBinary("client_id=" & clientId & 
                                    "&scope=https%3A%2F%2Fpurview.azure.net%2F.default" & 
                                    "&client_secret=" & clientSecret & 
                                    "&grant_type=client_credentials"),
            Headers = [#"Content-Type"="application/x-www-form-urlencoded"]
        ])),
    access_token = tokenResponse[access_token],

    // Call Purview Catalog API
    purviewUrl = "https://" & purviewAccountName & ".purview.azure.com/catalog/api/search/query?api-version=2021-05-01-preview",

    // Example Body - Adjust for your use case
    body = "{""keywords"":""*""}",

    purviewResponse = Json.Document(Web.Contents(purviewUrl,
        [
            Headers = [
                #"Authorization" = "Bearer " & access_token,
                #"Content-Type"="application/json"
            ],
            Content=Text.ToBinary(body),
            Method="POST"
        ])),

    // Convert API response to a table
    purviewData = purviewResponse[results],
    purviewTable = Table.FromList(purviewData, Splitter.SplitByNothing(), {"Record"})
in
    purviewTable

Explanation:

✔️ Authenticates to Azure AD using OAuth2
✔️ Queries Purview Catalog API
✔️ Retrieves catalog search results
✔️ Adaptable for custom queries (e.g., glossary, assets)

🟣 Step 6: Load into Power BI or Excel

You can now easily load this data inside:

✔️ Power BI Desktop
✔️ Excel Power Query Editor

Point to notes:

✔️ Purview API is not yet part of core Microsoft Graph but follows a similar structure
✔️ Catalog.Read.All permission is read-only
✔️ Expand records into columns easily inside Power Query Editor

Common Errors:

Error	Reason
Invalid client secret	Check if the client secret expired or is wrong
Insufficient privileges	Make sure you granted admin consent
401 Unauthorized	Wrong tenant ID, client ID, or missing scope