Custom PBi connector for MS Graph

[FilipSevcik]

Hi community,

i am trying to build custom connector for PBI in VS Code using Power Query SDK, but struggling to get it discoverable for pbi desktop while getting data; i have sample downloaded form github, pbi can see it, but not mine...extensions are enabled in pbi, .mez file i placed here: C:\Users\F004665\OneDrive - CEVA Logistics\Documents\Power BI Desktop\Custom Connectors

here is my code: tenantid, client secret and client id are filled based on registered app with proper permissions

.mez file is created with .pq and .png files

pls advice what i am doing wrong 😞 

[Version = "1.0.0"]

section MSGraphAPIConnector;

// Main function

[DataSource.Kind="MSGraphAPIConnector", Publish="MSGraphAPIConnector.Publish"]

shared MSGraphAPIConnector.Contents = () =>

    let

        // Define authentication parameters

        tenantId = "xxx",

        clientId = "xxx",

        clientSecret = "xxxx",

        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%2Fgraph.microsoft.com%2F.default" &

                                    "&client_secret=" & clientSecret &

                                    "&grant_type=client_credentials"),

            Headers = [

                #"Content-Type" = "application/x-www-form-urlencoded"

            ],

            ManualStatusHandling = {400, 401, 403}

        ])),

        accessToken = tokenResponse[access_token],

        // Define base URL for Graph API

        baseUrl = "https://graph.microsoft.com/beta/users?$select=displayName,givenName,surname,userPrincipalName,jobTi...",

        // Function to recursively get all pages

        GetAllPages = (url as text) =>

            let

                response = Json.Document(Web.Contents(url, [

                    Headers = [

                        Authorization = "Bearer " & accessToken,

                        Accept = "application/json"

                    ]

                ])),

                currentPage = response[value],

                nextLink = try response[#"@odata.nextLink"] otherwise null,

                morePages = if nextLink <> null then @GetAllPages(nextLink) else {},

                combined = List.Combine({currentPage, morePages})

            in

                combined,

        // Call the function

        allUsers = GetAllPages(baseUrl),

        // Convert to table

        usersTable = Table.FromRecords(allUsers),

        #"Expanded onPremisesExtensionAttributes" = Table.ExpandRecordColumn(usersTable, "onPremisesExtensionAttributes", {

            "extensionAttribute1", "extensionAttribute2", "extensionAttribute3", "extensionAttribute4", "extensionAttribute5",

            "extensionAttribute6", "extensionAttribute7", "extensionAttribute8", "extensionAttribute9", "extensionAttribute10",

            "extensionAttribute11", "extensionAttribute12", "extensionAttribute13", "extensionAttribute14", "extensionAttribute15"

        }, {

            "onPremisesExtensionAttributes.extensionAttribute1", "onPremisesExtensionAttributes.extensionAttribute2", "onPremisesExtensionAttributes.extensionAttribute3",

            "onPremisesExtensionAttributes.extensionAttribute4", "onPremisesExtensionAttributes.extensionAttribute5", "onPremisesExtensionAttributes.extensionAttribute6",

            "onPremisesExtensionAttributes.extensionAttribute7", "onPremisesExtensionAttributes.extensionAttribute8", "onPremisesExtensionAttributes.extensionAttribute9",

            "onPremisesExtensionAttributes.extensionAttribute10", "onPremisesExtensionAttributes.extensionAttribute11", "onPremisesExtensionAttributes.extensionAttribute12",

            "onPremisesExtensionAttributes.extensionAttribute13", "onPremisesExtensionAttributes.extensionAttribute14", "onPremisesExtensionAttributes.extensionAttribute15"

        }),

        // Add columns for skuid and disabledPlans

        #"Add Assigned License Columns" = Table.AddColumn(#"Expanded onPremisesExtensionAttributes", "skuid", each Text.Combine(List.Transform([assignedLicenses], each Record.FieldOrDefault(_, "skuId", "")), ";")),

        #"Add Disabled Plans Column" = Table.AddColumn(#"Add Assigned License Columns", "disabledPlans", each Text.Combine(List.Transform([assignedLicenses], each Text.Combine(List.Transform(Record.FieldOrDefault(_, "disabledPlans", {}), each Text.From(_)), ",")), ";"))

    in

        #"Add Disabled Plans Column";

// Data Source Kind description

MSGraphAPIConnector = [

    Label = "MSGraphAPIConnector"

];

// UI publishing description

MSGraphAPIConnector.Publish = [

    Beta = true,

    Category = "Other",

    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },

    LearnMoreUrl = "https://powerbi.microsoft.com/",

    SourceImage = MSGraphAPIConnector.Icons,

    SourceTypeImage = MSGraphAPIConnector.Icons

];

// Icons

MSGraphAPIConnector.Icons = [

    Icon16 = { Extension.Contents("MS Graph API Connector16.png"), Extension.Contents("MS Graph API Connector20.png"), Extension.Contents("MS Graph API Connector24.png"), Extension.Contents("MS Graph API Connector32.png") },

    Icon32 = { Extension.Contents("MS Graph API Connector32.png"), Extension.Contents("MS Graph API Connector40.png"), Extension.Contents("MS Graph API Connector48.png"), Extension.Contents("MS Graph API Connector64.png") }

];

[DataNinja777 · Accepted solution]

Hi @FilipSevcik ,

It's a common hurdle to get a custom connector to appear in Power BI Desktop for the first time. Since you've confirmed that a sample connector from GitHub is working, we can be confident that your Power BI settings and folder path are correct. This suggests the issue is within the code or the structure of your .mez file.

The most likely reason your connector isn't being discovered by Power BI is related to how you've defined the button text. Your code uses the Extension.LoadString function, which instructs Power BI to load text from a resources.resx file within your project. If this resource file is missing or doesn't contain the specified keys ("ButtonTitle", "ButtonHelp"), the connector metadata cannot be parsed correctly, and Power BI will silently ignore it.

Your current publishing code looks like this:

MSGraphAPIConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = MSGraphAPIConnector.Icons,
    SourceTypeImage = MSGraphAPIConnector.Icons
];

To quickly test if this is the cause, you can temporarily replace the Extension.LoadString calls with hardcoded text. After making this change, rebuild the .mez file and restart Power BI Desktop. The connector should now become visible in the "Get Data" dialog.

MSGraphAPIConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { "MS Graph API Connector", "Connect to Microsoft Graph API" },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = MSGraphAPIConnector.Icons,
    SourceTypeImage = MSGraphAPIConnector.Icons
];

While that should solve the discovery issue, there is a more fundamental architectural problem with the connector's authentication method. Hardcoding the client_id, client_secret, and tenantId is a significant security risk, as the secret is stored in plain text. Furthermore, this design will cause practical issues. The access token will expire, typically in an hour, causing any manual or scheduled refreshes to fail because your code does not handle token renewal. The proper, secure, and robust solution is to leverage Power Query's built-in authentication handlers.

For the Microsoft Graph API, you should use the Aad (Azure Active Directory) authentication kind. This allows the Power Query framework to manage the entire OAuth 2.0 sign-in process, including securely storing credentials and automatically refreshing tokens. A refactored and much-improved version of your connector would use OData.Feed, which is optimized for OData services like the Graph API, and would define the Aad authentication type. This eliminates all manual token handling and hardcoded secrets.

[Version = "1.0.0"]
section MSGraphAPIConnector;

[DataSource.Kind="MSGraphAPIConnector", Publish="MSGraphAPIConnector.Publish"]
shared MSGraphAPIConnector.Contents = () =>
    let
        source = OData.Feed("https://graph.microsoft.com/v1.0", null, [
            Implementation = "2.0"
        ])
    in
        source;

MSGraphAPIConnector = [
    Authentication = [
        Aad = [
            AuthorizationUri = "https://login.microsoftonline.com/common/oauth2/v2.0/authorize",
            Resource = "https://graph.microsoft.com"
        ]
    ],
    Label = "MS Graph API Connector (Recommended)"
];

MSGraphAPIConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { "MS Graph API Connector (Recommended)", "Connect to Microsoft Graph API" },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = MSGraphAPIConnector.Icons,
    SourceTypeImage = MSGraphAPIConnector.Icons
];

MSGraphAPIConnector.Icons = [
    Icon16 = { Extension.Contents("MSGraph16.png"), Extension.Contents("MSGraph20.png"), Extension.Contents("MSGraph24.png"), Extension.Contents("MSGraph32.png") },
    Icon32 = { Extension.Contents("MSGraph32.png"), Extension.Contents("MSGraph40.png"), Extension.Contents("MSGraph48.png"), Extension.Contents("MSGraph64.png") }
];

By implementing this revised code, you gain significant advantages. The OData.Feed function will automatically handle pagination (@odata.nextLink) and schema detection. The Authentication section tells Power BI to use a standard Microsoft sign-in prompt, and the framework will pass the necessary authorization token to the Graph API on every call. To make this work, you must update your app registration in the Azure portal. Under the "Authentication" section, add a platform for "Mobile and desktop applications" and set the redirect URI to https://oauth.powerbi.com/views/oauthredirect.html. Also, ensure you have configured the necessary delegated API permissions (like User.Read.All), as the user will be signing in and consenting on their own behalf. This approach results in a secure, robust, and shareable connector that functions correctly in both Power BI Desktop and the Power BI Service.

Best regards,

[FilipSevcik]

yes, thank you

[V-yubandi-msft]

Hi @FilipSevcik ,

Could you confirm if your issue is resolved, or if you need any additional details.

Thank you.

[FilipSevcik]

Hi @V-yubandi-msft ,

not sure how to change code based on your recommendation; can you specify pls?

[FilipSevcik]

thank you @V-yubandi-msft 

but still same issue, maybe configuration of app is wrong or missing?

[V-yubandi-msft]

It seems the problem could be related to your Azure App Registration settings. Please check that you have assigned the correct Microsoft Graph application permissions  and have clicked Grant admin consent after adding them. Also, verify that your client secret is still active and that you're using the actual secret value, not the ID. If your app should support multiple tenants, ensure it is set to multi tenant.

Thank You.

[V-yubandi-msft]

Thank you for your follow up. I’d like to clarify how you can update your code to resolve the issue.

The error message you’re seeing

“AADSTS9002326: The 'resource' request parameter is not supported.”

Occurs because the resource parameter is only supported with the older OAuth 1.0 endpoint.

Since you’re using the v2.0 endpoint

https://login.microsoftonline.com/common/oauth2/v2.0/authorize

you’ll need to use scope instead of resource. Here’s what to change.

Replace your old code:

Authentication = [
    Aad = [
        AuthorizationUri = "https://login.microsoftonline.com/common/oauth2/v2.0/authorize",
        Resource = "https://graph.microsoft.com"
    ]
]

With this updated code.

Authentication = [
    Aad = [
        AuthorizationUri = "https://login.microsoftonline.com/common/oauth2/v2.0/authorize",
        Scope = "https://graph.microsoft.com/.default"
    ]
]

FYI:

1. Scope is the correct way to declare permissions with the v2.0 endpoint.

2. "https://graph.microsoft.com/.default"  tells Azure to use the permissions you've already configured in the app registration.

3. No need to change anything else  your redirect URI and app permissions look good from the screenshots.

Thank You.

[V-yubandi-msft · Accepted solution]

Hi @FilipSevcik ,

Thank you for engaging with the Microsoft Fabric Community.

Error:

AADSTS9002326: The 'resource' request parameter is not supported

This means they are using the OAuth v2.0 endpoint with a resource parameter which is not allowed. The correct fix is to replace resource  with scope, as you correctly stated.

Regards,
Yugandhar.

[FilipSevcik]

Hi @DataNinja777 , thank you for your review and suggestion with explanation for improvment

i applied your script, Connector gets finally visible, but now facing sign in issue

can you pls advice where might be issue?

as for authentication in app, this is used: 

and these are the permissions i have: 

[DataNinja777 · Accepted solution]

Hi @FilipSevcik ,

It's a common hurdle to get a custom connector to appear in Power BI Desktop for the first time. Since you've confirmed that a sample connector from GitHub is working, we can be confident that your Power BI settings and folder path are correct. This suggests the issue is within the code or the structure of your .mez file.

The most likely reason your connector isn't being discovered by Power BI is related to how you've defined the button text. Your code uses the Extension.LoadString function, which instructs Power BI to load text from a resources.resx file within your project. If this resource file is missing or doesn't contain the specified keys ("ButtonTitle", "ButtonHelp"), the connector metadata cannot be parsed correctly, and Power BI will silently ignore it.

Your current publishing code looks like this:

MSGraphAPIConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = MSGraphAPIConnector.Icons,
    SourceTypeImage = MSGraphAPIConnector.Icons
];

To quickly test if this is the cause, you can temporarily replace the Extension.LoadString calls with hardcoded text. After making this change, rebuild the .mez file and restart Power BI Desktop. The connector should now become visible in the "Get Data" dialog.

MSGraphAPIConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { "MS Graph API Connector", "Connect to Microsoft Graph API" },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = MSGraphAPIConnector.Icons,
    SourceTypeImage = MSGraphAPIConnector.Icons
];

While that should solve the discovery issue, there is a more fundamental architectural problem with the connector's authentication method. Hardcoding the client_id, client_secret, and tenantId is a significant security risk, as the secret is stored in plain text. Furthermore, this design will cause practical issues. The access token will expire, typically in an hour, causing any manual or scheduled refreshes to fail because your code does not handle token renewal. The proper, secure, and robust solution is to leverage Power Query's built-in authentication handlers.

For the Microsoft Graph API, you should use the Aad (Azure Active Directory) authentication kind. This allows the Power Query framework to manage the entire OAuth 2.0 sign-in process, including securely storing credentials and automatically refreshing tokens. A refactored and much-improved version of your connector would use OData.Feed, which is optimized for OData services like the Graph API, and would define the Aad authentication type. This eliminates all manual token handling and hardcoded secrets.

[Version = "1.0.0"]
section MSGraphAPIConnector;

[DataSource.Kind="MSGraphAPIConnector", Publish="MSGraphAPIConnector.Publish"]
shared MSGraphAPIConnector.Contents = () =>
    let
        source = OData.Feed("https://graph.microsoft.com/v1.0", null, [
            Implementation = "2.0"
        ])
    in
        source;

MSGraphAPIConnector = [
    Authentication = [
        Aad = [
            AuthorizationUri = "https://login.microsoftonline.com/common/oauth2/v2.0/authorize",
            Resource = "https://graph.microsoft.com"
        ]
    ],
    Label = "MS Graph API Connector (Recommended)"
];

MSGraphAPIConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { "MS Graph API Connector (Recommended)", "Connect to Microsoft Graph API" },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = MSGraphAPIConnector.Icons,
    SourceTypeImage = MSGraphAPIConnector.Icons
];

MSGraphAPIConnector.Icons = [
    Icon16 = { Extension.Contents("MSGraph16.png"), Extension.Contents("MSGraph20.png"), Extension.Contents("MSGraph24.png"), Extension.Contents("MSGraph32.png") },
    Icon32 = { Extension.Contents("MSGraph32.png"), Extension.Contents("MSGraph40.png"), Extension.Contents("MSGraph48.png"), Extension.Contents("MSGraph64.png") }
];

By implementing this revised code, you gain significant advantages. The OData.Feed function will automatically handle pagination (@odata.nextLink) and schema detection. The Authentication section tells Power BI to use a standard Microsoft sign-in prompt, and the framework will pass the necessary authorization token to the Graph API on every call. To make this work, you must update your app registration in the Azure portal. Under the "Authentication" section, add a platform for "Mobile and desktop applications" and set the redirect URI to https://oauth.powerbi.com/views/oauthredirect.html. Also, ensure you have configured the necessary delegated API permissions (like User.Read.All), as the user will be signing in and consenting on their own behalf. This approach results in a secure, robust, and shareable connector that functions correctly in both Power BI Desktop and the Power BI Service.

Best regards,