Implementing Custom Data Connector Authentication and OAuth Flows in Power Query M Language

Introduction

You've built a Power Query connector that pulls data from a REST API using a hardcoded API key, and it works fine in development. Then your security team calls. They want OAuth 2.0, token refresh, per-user credentials, and they want the credentials stored nowhere near the M code itself. Suddenly "just calling Web.Contents" isn't enough, and you're staring at the Power Query SDK documentation wondering why every example stops right before the part you actually need.

Custom data connector authentication is one of those topics where the gap between "I know M" and "I know how to ship a production connector" is enormous. The authentication infrastructure in Power Query — credential storage, OAuth flows, token refresh, capability negotiation — is deeply integrated with the host application (Power BI Desktop, Power BI Service, Excel) and operates through a set of conventions that aren't fully documented in one place. This lesson closes that gap. We'll build a complete, production-ready connector that implements OAuth 2.0 Authorization Code flow with PKCE, handles token refresh transparently, and degrades gracefully to API key authentication for service-to-service scenarios.

By the end of this lesson, you'll have shipped a connector that your security team will actually approve.

What you'll learn:

• How the Power Query credential system works internally, and why you can't just store tokens in variables
• Implementing OAuth 2.0 Authorization Code flow with PKCE inside a .pqx connector
• Building transparent token refresh that doesn't require the user to re-authenticate
• Implementing Key and UsernamePassword authentication as fallback or alternative schemes
• Structuring multi-authentication connectors that let users choose their credential type
• Testing authentication flows locally with the Power Query SDK test harness

---

Prerequisites

This is an expert-level lesson. Before diving in, you should be comfortable with:

• Writing M functions, records, and custom types
• The basic structure of a .pq or .pqx connector project (.pq source file, resources.resx, connector metadata)
• REST API fundamentals: HTTP headers, OAuth 2.0 terminology (authorization code, access token, refresh token, scopes)
• Visual Studio Code with the Power Query SDK extension installed, or Visual Studio with the legacy Power Query SDK
• Basic familiarity with Web.Contents and its ManualCredentials option

If you haven't built a basic custom connector yet, work through the official "Hello World" connector tutorial first. The authentication infrastructure we're building here sits on top of that foundation.

---

How the Power Query Credential System Actually Works

Before writing a single line of authentication code, you need a mental model of how credentials flow through the system. Most tutorials skip this, which is why developers end up confused when their token seems to vanish between evaluations.

The Power Query engine separates the definition of how credentials work from the storage of actual credentials. Your connector's M code declares what authentication schemes it supports and what the OAuth flow looks like. The host application — Power BI Desktop, the Power BI Service gateway, Excel — owns the credential vault. It stores tokens encrypted, associates them with a data source path, and provides them back to your connector at evaluation time through a set of extension points.

This design has a critical implication: you cannot store credentials in M variables between evaluations. Each time your connector's data-fetching function runs, it's a fresh evaluation. There's no session object, no persistent state, no "remember this token." The host application injects credentials into your connector through the Extension.CurrentCredential() function, and that's your only window into what the user authenticated with.

Here's what Extension.CurrentCredential() returns for an OAuth token:

[
    AuthenticationKind = "OAuth",
    access_token = "eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9...",
    refresh_token = "8xLOxBtZp8",
    token_type = "Bearer",
    expires_in = 3600,
    expires_on = #datetime(2024, 3, 15, 14, 30, 0)
]

Notice expires_on. The host application tracks token expiry and will call your TokenMethod function automatically before your data function runs if the token has expired. You don't refresh tokens inside your data function — the credential lifecycle is managed entirely by the host.

This separation of concerns is elegant once you understand it, but it means the authentication code you write is really a protocol definition, not an authentication execution engine.

The Data Source Path and Credential Matching

The host application associates credentials with a data source path, which is determined by the DataSource.Path metadata on your data source function. If your connector's function signature is:

[DataSource.Kind = "MyConnector", Publish = "MyConnector.Publish"]
shared MyConnector.Contents = (baseUrl as text, optional options as record) as table =>

The data source path is derived from the baseUrl parameter. Every unique baseUrl value gets a separate credential entry. This matters for multi-tenant APIs: a user connecting to https://api.contoso.com and https://api.fabrikam.com through the same connector will have two separate credential sets.

If you want all connections through your connector to share a single credential (common for SaaS APIs where the tenant is determined by the token itself), you can override DataSource.Path to suppress path-specific matching:

MyConnector = [
    Authentication = [
        OAuth = [
            StartLogin = MyConnector.StartLogin,
            FinishLogin = MyConnector.FinishLogin,
            Refresh = MyConnector.Refresh,
            Logout = MyConnector.Logout
        ]
    ],
    Label = "My Connector",
    // Suppress URL-based path differentiation
    SupportsEncryption = true
];

We'll come back to path configuration when we build the full connector.

---

Setting Up the Connector Project

Create a new connector project in VS Code using the Power Query SDK. Your project structure should look like this:

MyConnector/
├── MyConnector.pq          ← Main connector code
├── MyConnector.query.pq    ← Test queries
├── resources.resx          ← String resources
└── MyConnector.pqx         ← Built output (generated)

In resources.resx, add the string resources you'll need:

<data name="MyConnector_Name" xml:space="preserve">
  <value>My API Connector</value>
</data>
<data name="MyConnector_Label" xml:space="preserve">
  <value>My API</value>
</data>

Now let's build the connector piece by piece.

---

Declaring Authentication Schemes

Every connector starts with a definition record that declares its authentication capabilities. This is the contract between your connector and the host application. Let's define a connector that supports both OAuth 2.0 and API Key authentication:

section MyConnector;

// ─── Connector Definition ────────────────────────────────────────────────────

MyConnector = [
    Authentication = [
        OAuth = [
            StartLogin  = MyConnector.StartLogin,
            FinishLogin = MyConnector.FinishLogin,
            Refresh     = MyConnector.Refresh,
            Logout      = MyConnector.Logout
        ],
        Key = []
    ],
    Label = Extension.LoadString("MyConnector_Label"),
    SupportsEncryption = true
];

MyConnector.Publish = [
    Beta = false,
    Category = "Other",
    ButtonText = { Extension.LoadString("MyConnector_Name"), Extension.LoadString("MyConnector_Label") },
    LearnMoreUrl = "https://docs.myapi.com",
    SourceImage = MyConnector.Icons,
    SourceTypeImage = MyConnector.Icons
];

MyConnector.Icons = [
    Icon16 = Extension.Contents("MyConnector16.png"),
    Icon32 = Extension.Contents("MyConnector32.png"),
    Icon48 = Extension.Contents("MyConnector48.png"),
    Icon64 = Extension.Contents("MyConnector64.png")
];

The Authentication record is where everything starts. Each key in this record corresponds to an AuthenticationKind that the host will present to the user. By declaring both OAuth and Key, users see a dropdown in Power BI Desktop's credential dialog where they can choose between "OAuth2" and "API Key."

The Key authentication kind is the simplest — it just tells the host to show a text box and store whatever the user types. Your connector retrieves it with:

Extension.CurrentCredential()[Key]

That's it for API key auth. OAuth is considerably more involved.

Important: The SupportsEncryption = true flag is not optional for production connectors. Without it, Power BI Service will refuse to use your connector through an on-premises data gateway with an encrypted connection.

---

Implementing OAuth 2.0 Authorization Code Flow

OAuth 2.0 Authorization Code flow is the gold standard for user-delegated access. The user logs into the API provider's own login page, consents to your application's requested scopes, and the provider redirects back to Power BI with an authorization code. Power BI then exchanges that code for tokens. Your connector orchestrates this dance through four functions: StartLogin, FinishLogin, Refresh, and Logout.

The OAuth Configuration Constants

Define your OAuth parameters at the top of your connector file. Keep them as named constants rather than magic strings buried in functions:

// ─── OAuth Configuration ──────────────────────────────────────────────────

// Replace with your actual OAuth application credentials
// In production, the client_secret is embedded in the connector .pqx file,
// which itself should be kept in a secure build pipeline.
ClientId     = "your-app-client-id";
ClientSecret = "your-app-client-secret";

// The base URL of the API we're connecting to
BaseUrl      = "https://api.myservice.com/v2";

// OAuth endpoints
AuthorizeUrl = "https://auth.myservice.com/oauth2/authorize";
TokenUrl     = "https://auth.myservice.com/oauth2/token";
LogoutUrl    = "https://auth.myservice.com/oauth2/logout";

// Redirect URI that Power BI listens on during the OAuth flow
// This is a well-known URI registered in your OAuth application
RedirectUri  = "https://oauth.powerbi.com/views/oauthredirect.html";

// Scopes your connector needs
DefaultScopes = "data.read data.export offline_access";

The RedirectUri deserves special attention. https://oauth.powerbi.com/views/oauthredirect.html is Power BI's registered OAuth redirect handler. When you register your OAuth application with the API provider, you must whitelist this URI. The Power BI Desktop client intercepts navigations to this URL and extracts the authorization code from the query string — it's a browser intercept pattern, not an actual HTTP endpoint.

For local testing with the Power Query SDK, you can also use https://preview.powerbi.com/views/oauthredirect.html.

StartLogin: Initiating the Authorization Request

StartLogin is called when the user clicks "Sign In" in the credential dialog. Its job is to construct the authorization URL and return it to the host, along with any state the connector needs to complete the flow:

MyConnector.StartLogin = (resourceUrl as text, state as text, display as text) =>
    let
        // Generate PKCE code verifier and challenge
        // PKCE (Proof Key for Code Exchange) prevents authorization code interception attacks
        CodeVerifier  = MyConnector.GenerateCodeVerifier(),
        CodeChallenge = MyConnector.GenerateCodeChallenge(CodeVerifier),

        AuthorizeParameters = [
            client_id             = ClientId,
            response_type         = "code",
            redirect_uri          = RedirectUri,
            scope                 = DefaultScopes,
            state                 = state,
            code_challenge        = CodeChallenge,
            code_challenge_method = "S256"
        ],

        AuthorizeUrlWithParams = AuthorizeUrl & "?" & Uri.BuildQueryString(AuthorizeParameters)
    in
        [
            LoginUri      = AuthorizeUrlWithParams,
            CallbackUri   = RedirectUri,
            WindowHeight  = 720,
            WindowWidth   = 1024,
            // Pass the code verifier back through state so FinishLogin can use it
            Context       = CodeVerifier
        ];

The return record from StartLogin tells the host how to open the OAuth popup: the URL to navigate to, the redirect URI to watch for (so it knows when the flow is complete), and the popup dimensions. The Context field is particularly important — it's an opaque value that the host will pass to FinishLogin as the context parameter. This is your only mechanism for carrying state between StartLogin and FinishLogin, and we're using it to carry the PKCE code verifier.

Implementing PKCE

PKCE (Proof Key for Code Exchange, RFC 7636) is now required by most modern OAuth servers and strongly recommended by the OAuth 2.0 security best practices RFC. In M, we implement it using the Crypto.CreateHash function:

// Generates a cryptographically random code verifier (43-128 chars, URL-safe)
MyConnector.GenerateCodeVerifier = () =>
    let
        // Generate 32 random bytes and base64url-encode them
        // Binary.Random is available in Power Query M
        RandomBytes   = Binary.Random(32),
        Base64        = Binary.ToText(RandomBytes, BinaryEncoding.Base64),
        // Convert standard Base64 to Base64url (RFC 4648 §5)
        Base64Url     = Text.Replace(
                            Text.Replace(
                                Text.Replace(Base64, "+", "-"),
                            "/", "_"),
                        "=", "")
    in
        Base64Url;

// Generates the S256 code challenge from a verifier
MyConnector.GenerateCodeChallenge = (verifier as text) =>
    let
        VerifierBytes  = Text.ToBinary(verifier, TextEncoding.Ascii),
        HashBytes      = Crypto.CreateHash(CryptoAlgorithm.SHA256, VerifierBytes),
        Base64         = Binary.ToText(HashBytes, BinaryEncoding.Base64),
        // Convert to Base64url
        Base64Url      = Text.Replace(
                             Text.Replace(
                                 Text.Replace(Base64, "+", "-"),
                             "/", "_"),
                         "=", "")
    in
        Base64Url;

Warning: Binary.Random generates cryptographically suitable random bytes in the Power Query engine, but it's not documented explicitly as cryptographically secure. For connectors where the security bar is extremely high (financial data, healthcare), consider adding a note in your security documentation that PKCE here provides transport-level protection but the code verifier entropy depends on the M runtime's random implementation.

FinishLogin: Exchanging the Code for Tokens

After the user authenticates in the popup, the host intercepts the redirect to RedirectUri and calls FinishLogin with the full callback URL. Your job is to extract the authorization code and exchange it for tokens:

MyConnector.FinishLogin = (context as text, callbackUri as text, state as text) =>
    let
        // Parse the authorization code from the callback URL
        Parts         = Uri.Parts(callbackUri),
        QueryParams   = Parts[Query],
        Code          = Record.Field(QueryParams, "code"),

        // The context carries our PKCE code verifier from StartLogin
        CodeVerifier  = context,

        // Exchange the authorization code for tokens
        TokenResponse = MyConnector.TokenRequest([
            grant_type    = "authorization_code",
            code          = Code,
            redirect_uri  = RedirectUri,
            code_verifier = CodeVerifier
        ])
    in
        TokenResponse;

The state parameter here is the same opaque state value passed through from StartLogin. You should validate that QueryParams[state] matches the state parameter to protect against CSRF attacks:

MyConnector.FinishLogin = (context as text, callbackUri as text, state as text) =>
    let
        Parts          = Uri.Parts(callbackUri),
        QueryParams    = Parts[Query],

        // CSRF validation: the state we sent must match the state we received
        ReturnedState  = Record.FieldOrDefault(QueryParams, "state", ""),
        _ValidateState = if ReturnedState <> state
                         then error Error.Record("Security", 
                              "OAuth state mismatch — possible CSRF attack", 
                              [Expected = state, Received = ReturnedState])
                         else null,

        Code          = Record.Field(QueryParams, "code"),
        CodeVerifier  = context,

        TokenResponse = MyConnector.TokenRequest([
            grant_type    = "authorization_code",
            code          = Code,
            redirect_uri  = RedirectUri,
            code_verifier = CodeVerifier
        ])
    in
        TokenResponse;

Note: The _ValidateState = ... pattern is an M idiom for side-effect validation in an expression-oriented language. We assign the error-or-null to a name prefixed with underscore (convention for "intentionally unused"), and the let evaluation will throw if the error is reached.

The Token Request Helper

Both FinishLogin and Refresh need to make token endpoint requests. Centralize this logic:

MyConnector.TokenRequest = (body as record) =>
    let
        // Merge client credentials into the body
        FullBody = Record.Combine([
            body,
            [
                client_id     = ClientId,
                client_secret = ClientSecret
            ]
        ]),

        Response = Web.Contents(TokenUrl, [
            Content = Text.ToBinary(Uri.BuildQueryString(FullBody), TextEncoding.Ascii),
            Headers = [
                #"Content-Type" = "application/x-www-form-urlencoded",
                Accept          = "application/json"
            ],
            ManualCredentials = true  // Critical: prevents the engine from injecting its own auth
        ]),

        JsonResponse = Json.Document(Response),

        // Validate the response contains what we expect
        Result = if Record.HasFields(JsonResponse, {"access_token"})
                 then JsonResponse
                 else error Error.Record("TokenError",
                      "Token endpoint did not return an access_token",
                      JsonResponse)
    in
        Result;

The ManualCredentials = true option on Web.Contents is non-negotiable here. Without it, the Power Query engine will attempt to use whatever credential is currently stored for the token URL's domain, which will either fail or inject the wrong credential into your token request. Always set ManualCredentials = true when making authentication requests that you control manually.

Refresh: Transparent Token Renewal

The Refresh function is called by the host when the stored access token has expired. The host passes in the current credential record (containing the refresh token) and expects back a new credential record:

MyConnector.Refresh = (resourceUrl as text, oldCredential as record) =>
    let
        RefreshToken  = oldCredential[refresh_token],

        TokenResponse = MyConnector.TokenRequest([
            grant_type    = "refresh_token",
            refresh_token = RefreshToken,
            scope         = DefaultScopes
        ])
    in
        TokenResponse;

Some OAuth servers issue a new refresh token with every refresh response (the "refresh token rotation" pattern). Others reuse the same refresh token indefinitely. Your connector handles both cases correctly because it simply returns whatever the token endpoint gives back — if there's a new refresh_token in the response, the host stores it. If there isn't, the host keeps the old one.

However, there's a subtle edge case: some OAuth servers expire refresh tokens after a period of inactivity (say, 90 days). When this happens, the Refresh call will fail with an HTTP 400 or 401 from the token endpoint. Your connector should propagate this as a re-authentication request rather than a generic error:

MyConnector.Refresh = (resourceUrl as text, oldCredential as record) =>
    let
        RefreshToken = oldCredential[refresh_token],

        // Attempt the refresh, catching token expiry errors
        TokenResponse = try MyConnector.TokenRequest([
            grant_type    = "refresh_token",
            refresh_token = RefreshToken,
            scope         = DefaultScopes
        ]) otherwise
            // If the refresh failed, signal that the user needs to re-authenticate
            // by returning an error with the "DataSource.Error" kind
            error Error.Record("DataSource.Error",
                  "Your session has expired. Please sign in again.",
                  [ManuallyHandled = false])
    in
        TokenResponse;

Logout: Revoking the Token

The Logout function is called when the user explicitly disconnects their credentials in Power BI. Best practice is to revoke the token at the authorization server so the session is invalidated server-side:

MyConnector.Logout = (accessToken as text) =>
    let
        // Some providers have a revocation endpoint (RFC 7009)
        // Others just need us to return the logout URL for the browser to navigate to
        LogoutParameters = [
            token           = accessToken,
            token_type_hint = "access_token",
            client_id       = ClientId
        ],

        // Attempt server-side revocation
        _Revoke = try Web.Contents(
            "https://auth.myservice.com/oauth2/revoke",
            [
                Content           = Text.ToBinary(Uri.BuildQueryString(LogoutParameters), TextEncoding.Ascii),
                Headers           = [#"Content-Type" = "application/x-www-form-urlencoded"],
                ManualCredentials = true
            ]
        ) otherwise null
    in
        // Return the logout URL for the host to navigate the browser to
        // (clears any browser session cookies)
        LogoutUrl & "?" & Uri.BuildQueryString([client_id = ClientId, post_logout_redirect_uri = RedirectUri]);

The Logout function can return either null (silent logout, no browser navigation) or a URL string (host will open this in a browser for the user to confirm logout). For most enterprise scenarios, returning the logout URL and clearing the browser session is the right call.

---

Building the Data Access Layer

With authentication infrastructure in place, let's build the actual data-fetching function. The key is how you use Extension.CurrentCredential() to inject the right credentials:

[DataSource.Kind = "MyConnector", Publish = "MyConnector.Publish"]
shared MyConnector.Contents = (baseUrl as text, optional options as nullable record) as table =>
    let
        // Resolve options with defaults
        DefaultOptions = [
            PageSize = 1000,
            Timeout  = #duration(0, 0, 2, 0)
        ],
        ResolvedOptions = if options = null
                          then DefaultOptions
                          else Record.Combine([DefaultOptions, options]),

        // Build headers based on the current authentication kind
        AuthHeaders = MyConnector.GetAuthHeaders(),

        // Fetch the first page to discover total count
        FirstPage = MyConnector.GetPage(baseUrl, AuthHeaders, 1, ResolvedOptions[PageSize]),

        TotalCount = FirstPage[meta][total],
        PageCount  = Number.RoundUp(TotalCount / ResolvedOptions[PageSize]),

        // Generate all page numbers
        PageNumbers = List.Generate(
            () => 1,
            (page) => page <= PageCount,
            (page) => page + 1
        ),

        // Fetch all pages
        AllPages = List.Transform(
            PageNumbers,
            (page) => MyConnector.GetPage(baseUrl, AuthHeaders, page, ResolvedOptions[PageSize])[data]
        ),

        // Combine into a single table
        Combined = Table.FromList(
            List.Combine(AllPages),
            Splitter.SplitByNothing(),
            null,
            null,
            ExtraValues.Error
        ),

        // Expand the record column
        Result = Table.ExpandRecordColumn(
            Combined,
            "Column1",
            {"id", "name", "value", "created_at", "updated_at"},
            {"ID", "Name", "Value", "Created", "Updated"}
        )
    in
        Result;

Getting Authentication Headers

The GetAuthHeaders function is where Extension.CurrentCredential() gets called. It must handle both OAuth tokens and API keys:

MyConnector.GetAuthHeaders = () =>
    let
        Credential = Extension.CurrentCredential(),
        AuthKind   = Credential[AuthenticationKind],

        Headers = if AuthKind = "OAuth" then
            [
                Authorization = "Bearer " & Credential[access_token],
                #"X-Client"   = "PowerBI-Connector/1.0"
            ]
        else if AuthKind = "Key" then
            [
                #"X-API-Key"  = Credential[Key],
                #"X-Client"   = "PowerBI-Connector/1.0"
            ]
        else
            error Error.Record("AuthError",
                  "Unsupported authentication kind: " & AuthKind,
                  null)
    in
        Headers;

Tip: Never log, store, or return Extension.CurrentCredential() values in error messages or query results. Treat the entire return value of Extension.CurrentCredential() as highly sensitive. Even in development, be careful with diagnostic output.

Pagination with Error Handling

MyConnector.GetPage = (baseUrl as text, headers as record, page as number, pageSize as number) =>
    let
        QueryParams = [
            page      = Number.ToText(page),
            page_size = Number.ToText(pageSize)
        ],

        Url = baseUrl & "/items?" & Uri.BuildQueryString(QueryParams),

        Response = Web.Contents(Url, [
            Headers           = headers,
            ManualCredentials = true
        ]),

        StatusCode = Value.Metadata(Response)[Response.Status],

        // Explicit status code handling before parsing JSON
        // so we get useful errors rather than JSON parse failures
        ValidatedResponse = if StatusCode = 200 then Response
                            else if StatusCode = 401 then
                                error Error.Record("DataSource.Error",
                                    "Authentication failed — credentials may have expired",
                                    [HttpStatus = StatusCode])
                            else if StatusCode = 403 then
                                error Error.Record("DataSource.Error",
                                    "Access denied — check your account permissions",
                                    [HttpStatus = StatusCode])
                            else if StatusCode = 429 then
                                error Error.Record("DataSource.Error",
                                    "Rate limit exceeded — reduce query frequency",
                                    [HttpStatus = StatusCode])
                            else
                                error Error.Record("DataSource.Error",
                                    "API returned HTTP " & Number.ToText(StatusCode),
                                    [HttpStatus = StatusCode, Url = Url]),

        JsonResult = Json.Document(ValidatedResponse)
    in
        JsonResult;

One subtlety here: accessing Value.Metadata(Response)[Response.Status] requires that Web.Contents be configured to not throw on non-2xx responses. By default, Web.Contents will throw on 4xx/5xx responses before you can inspect the status code. Add IsRetry = false as a signal to avoid automatic retry behavior, and handle status checking yourself.

Actually, to get the status code before Web.Contents throws, use the ManualStatusHandling option:

Response = Web.Contents(Url, [
    Headers              = headers,
    ManualCredentials    = true,
    ManualStatusHandling = {400, 401, 403, 429, 500, 503}
]),

Listing status codes in ManualStatusHandling tells the engine not to throw for those codes, giving you the raw response to inspect. This is the correct approach.

---

Implementing UsernamePassword Authentication

For APIs that use HTTP Basic Auth or a username/password login endpoint, the UsernamePassword authentication kind is the right choice:

MyConnector_AltAuth = [
    Authentication = [
        UsernamePassword = [
            UsernameLabel = "API Username",
            PasswordLabel = "API Password",
            Label         = "Basic Authentication"
        ]
    ],
    Label = "My Connector (Basic Auth)"
];

Retrieving the credentials:

MyConnector.GetBasicAuthHeaders = () =>
    let
        Credential = Extension.CurrentCredential(),
        Username   = Credential[Username],
        Password   = Credential[Password],

        // Build the Basic auth header value
        Combined   = Username & ":" & Password,
        Encoded    = Binary.ToText(
                         Text.ToBinary(Combined, TextEncoding.Ascii),
                         BinaryEncoding.Base64
                     )
    in
        [ Authorization = "Basic " & Encoded ];

For APIs that require a login call (not HTTP Basic, but a form-post that returns a session token), you'll need a hybrid approach. Implement the UsernamePassword kind but in your data function, make a login call first, use the returned session token for subsequent requests, and let the host cache the session token as the access_token in the OAuth credential record. This is architecturally awkward — the OAuth flow maps better to this pattern — but it's sometimes necessary for legacy systems.

---

Multi-Authentication Connector Architecture

When you declare multiple authentication schemes, the host presents the user with a choice. The tricky part is that your data function must handle whichever scheme was chosen without knowing in advance. The Extension.CurrentCredential()[AuthenticationKind] field is your discriminator.

A clean pattern is to centralize credential resolution into a single function that all data functions call:

// Returns a record with two fields:
// - Headers: the HTTP headers to inject into requests
// - Scheme: the authentication kind being used (for logging/telemetry)
MyConnector.ResolveCredentials = () =>
    let
        Credential = Extension.CurrentCredential(),
        AuthKind   = Credential[AuthenticationKind]
    in
        if AuthKind = "OAuth" then [
            Headers = [
                Authorization = "Bearer " & Credential[access_token],
                #"X-Auth-Scheme" = "oauth2"
            ],
            Scheme = "OAuth"
        ]
        else if AuthKind = "Key" then [
            Headers = [
                #"X-API-Key" = Credential[Key],
                #"X-Auth-Scheme" = "apikey"
            ],
            Scheme = "Key"
        ]
        else if AuthKind = "UsernamePassword" then [
            Headers = [
                Authorization = "Basic " & Binary.ToText(
                    Text.ToBinary(Credential[Username] & ":" & Credential[Password], TextEncoding.Ascii),
                    BinaryEncoding.Base64
                ),
                #"X-Auth-Scheme" = "basic"
            ],
            Scheme = "UsernamePassword"
        ]
        else
            error Error.Record("AuthError", "Unrecognized authentication kind: " & AuthKind, null);

Every data function then calls MyConnector.ResolveCredentials() and uses the returned Headers record. This gives you a single place to update if you add a new auth scheme, and it keeps the data-fetching logic completely decoupled from authentication mechanics.

---

Testing Authentication Flows Locally

Testing OAuth flows locally is the most painful part of connector development. The Power Query SDK provides a credential test harness, but it requires some setup.

Setting Up Test Credentials

In your MyConnector.query.pq test file, you can simulate credential injection using #shared and the SDK's credential injection mechanism. Create a file called credentials.json in your project (add to .gitignore immediately):

{
  "MyConnector": {
    "AuthenticationKind": "Key",
    "Key": "your-test-api-key-here"
  }
}

Then in the VS Code Power Query SDK, use "Set credential" command to inject this into the test runner. For OAuth, you'll need to complete an actual OAuth flow through the SDK's browser integration.

Unit Testing Token Functions

You can test your PKCE generation independently:

// In MyConnector.query.pq

// Test PKCE generation
let
    Verifier   = MyConnector.GenerateCodeVerifier(),
    Challenge  = MyConnector.GenerateCodeChallenge(Verifier),

    // Verifier should be 43-86 characters (base64url of 32 bytes)
    VerifierLengthOk = Text.Length(Verifier) >= 43 and Text.Length(Verifier) <= 86,

    // Challenge should be 43 characters (SHA256 = 32 bytes, base64url = 43 chars)
    ChallengeLengthOk = Text.Length(Challenge) = 43,

    // Neither should contain +, /, or = (base64url requirements)
    VerifierIsUrl  = not Text.Contains(Verifier, "+") and not Text.Contains(Verifier, "/"),
    ChallengeIsUrl = not Text.Contains(Challenge, "+") and not Text.Contains(Challenge, "/")
in
    [
        VerifierSample     = Text.Start(Verifier, 10) & "...",
        VerifierLengthOk   = VerifierLengthOk,
        ChallengeLengthOk  = ChallengeLengthOk,
        VerifierIsUrl      = VerifierIsUrl,
        ChallengeIsUrl     = ChallengeIsUrl,
        AllTestsPassed     = VerifierLengthOk and ChallengeLengthOk and VerifierIsUrl and ChallengeIsUrl
    ]

Mocking the Token Endpoint

For integration tests where you want to test FinishLogin without a real OAuth server, set up a local HTTP mock (using a tool like Mockoon or WireMock) and temporarily override TokenUrl in your test build. This is also useful for testing error paths — configure your mock to return a 400 with an {"error": "invalid_grant"} body and verify your connector produces a useful error message.

---

Advanced Pattern: Custom Authentication UI with Windows Authentication Passthrough

Some enterprise scenarios require Windows Authentication (Kerberos/NTLM) to APIs hosted on-premises. The Windows authentication kind handles this:

MyEnterpriseConnector = [
    Authentication = [
        Windows = [
            SupportsAlternateCredentials = true
        ]
    ],
    Label = "Enterprise API"
];

With SupportsAlternateCredentials = true, users can enter alternate Windows credentials rather than using their current session identity. When Windows auth is active, Extension.CurrentCredential() returns:

[
    AuthenticationKind = "Windows",
    Username = "DOMAIN\\username",
    Password = "password"  // Only if alternate credentials were provided
]

Without alternate credentials, the engine handles Windows authentication transparently via SSPI — you don't need to inject any headers. Just set ManualCredentials = false (the default) on Web.Contents and the engine handles the Negotiate/Kerberos exchange automatically.

Architecture note: Windows auth passthrough only works in Power BI Desktop and gateway scenarios. It does not work in Power BI Service direct query mode to cloud endpoints. If you need both cloud and on-premises scenarios, implement both Windows and OAuth schemes and let users choose based on their environment.

---

Security Considerations and Anti-Patterns

Anti-Pattern: Storing Credentials in Query Parameters

Never pass credentials as URL query parameters, even for API key auth:

// BAD: API key visible in URL logs, browser history, proxy logs
Url = BaseUrl & "/data?api_key=" & Credential[Key]

// GOOD: API key in a header
Response = Web.Contents(BaseUrl & "/data", [
    Headers = [#"X-API-Key" = Credential[Key]],
    ManualCredentials = true
])

Power Query logs (visible in Fiddler and other HTTP proxies) will capture query strings. Headers are also captured, but at least you can suppress them with appropriate proxy configuration.

Anti-Pattern: Using `error` Records to Smuggle Credentials into Errors

When constructing error records, never include credential values in the Detail record:

// BAD: Token leaked into error output
error Error.Record("Error", "Request failed", [Token = Credential[access_token], Status = 403])

// GOOD: Sanitized error detail
error Error.Record("DataSource.Error", "Access denied", [Status = 403, Resource = Url])

Error details can appear in Power BI Service activity logs, gateway logs, and UI error dialogs.

Anti-Pattern: Conditional Bypass of ManualCredentials

Some developers set ManualCredentials = false on API calls when they think the domain matches the connector's declared domain, to let the engine inject credentials automatically. This is fragile — the engine's automatic credential injection behavior isn't fully specified and can change between Power BI releases. Always be explicit with ManualCredentials = true and inject credentials yourself.

Client Secret Handling

Embedding ClientSecret in your connector .pqx file is a pragmatic necessity for many OAuth flows, but it's not without risk. The .pqx file is a signed ZIP archive — a determined attacker can extract and read the embedded secret. Mitigations:

1. Treat the client secret as a public value for public clients (mobile apps, desktop apps) — configure your OAuth application to not rely on client secret confidentiality
2. Use the client_credentials grant with certificate authentication if your OAuth provider supports it (the private key lives outside the connector)
3. For high-security scenarios, proxy the token exchange through your own backend service, so the connector never handles the client secret directly

---

Hands-On Exercise

Build a complete connector for a fictional project management API with the following requirements:

Exercise Scenario: You're building a Power BI connector for "TaskFlow API" (https://api.taskflow.example/v1). The API supports two authentication methods:

1. OAuth 2.0 with PKCE (for interactive users)
2. Personal Access Token / API key (for service-to-service scenarios)

The API has two endpoints:

• GET /projects — returns a paginated list of projects
• GET /projects/{id}/tasks — returns tasks for a specific project

What to build:

1. Create the connector definition record with both OAuth and Key authentication schemes.

2. Implement all four OAuth functions (StartLogin, FinishLogin, Refresh, Logout) using these OAuth endpoints:

   • Authorize: https://auth.taskflow.example/oauth2/auth
   • Token: https://auth.taskflow.example/oauth2/token
   • Redirect: https://oauth.powerbi.com/views/oauthredirect.html

3. Write a TaskFlow.Projects data function that:

   • Handles both OAuth and Key authentication via a shared credential resolver
   • Paginates through all pages of the /projects endpoint (assume the API returns {"data": [...], "meta": {"page": 1, "total_pages": 5}})
   • Returns a properly typed table with columns: ProjectId (Int64), Name (text), Status (text), CreatedDate (date)

4. Write a TaskFlow.Tasks data function that accepts a projectId parameter and fetches all tasks for that project.

5. Write a test query that calls TaskFlow.Projects and then uses Table.AddColumn with each TaskFlow.Tasks([ProjectId]) to create a nested table of tasks per project.

Challenge: Add rate-limit handling to your page fetching function. The TaskFlow API returns a Retry-After header on HTTP 429 responses. While M itself can't sleep, implement graceful error messaging that extracts and surfaces the Retry-After value.

---

Common Mistakes and Troubleshooting

Mistake: Forgetting ManualCredentials = true on Token Requests

Symptom: Token endpoint calls fail with a 401 or the engine prompts for credentials to the token URL domain.

Cause: Without ManualCredentials = true, the engine tries to apply the connector's stored credential to the token URL request. Since no credential is stored for the auth server domain specifically, this either fails or prompts the user.

Fix: Always set ManualCredentials = true on any Web.Contents call where you're constructing the authentication yourself.

Mistake: OAuth Redirect URI Mismatch

Symptom: The OAuth login popup shows an error from the authorization server: "invalid_redirect_uri" or "redirect_uri_mismatch."

Cause: The redirect_uri in your StartLogin function doesn't exactly match what's registered in your OAuth application at the provider.

Fix: Copy the redirect URI character-for-character from your OAuth application registration. For Power BI, use https://oauth.powerbi.com/views/oauthredirect.html exactly. Some providers are case-sensitive; some require a trailing slash; some require the query string to be absent.

Mistake: Context Not Passed Through PKCE Flow

Symptom: FinishLogin fails because context is null or empty.

Cause: The Context field in StartLogin's return record was null or omitted.

Fix: Verify that StartLogin returns a non-null Context field. Some older Power Query SDK versions serialize the Context differently — test with a simple string value first, then use the actual code verifier.

Mistake: Using Web.Contents Without ManualStatusHandling for Error Pages

Symptom: When the API returns a 401, instead of your custom error message, the user sees a JSON parse error because Web.Contents returned the error body and then threw before you could inspect the status code.

Cause: Web.Contents throws by default on 4xx/5xx responses.

Fix: Add ManualStatusHandling = {401, 403, 404, 429, 500} to your Web.Contents options, then inspect Value.Metadata(Response)[Response.Status] before parsing the response body.

Mistake: Token Refresh Failure Causes Silent Data Loss

Symptom: After token expiry, refreshes fail, but instead of prompting re-authentication, the connector returns empty tables or null values.

Cause: The Refresh function swallowed the exception with try ... otherwise null and returned null, which the host interprets as an empty credential.

Fix: In your Refresh function's error handling, always re-raise as a DataSource.Error — never return null from Refresh. The host knows how to handle DataSource.Error from Refresh by prompting re-authentication.

Mistake: Hard-coding the Scope in Token Refresh

Symptom: Token refresh succeeds but the refreshed token has fewer permissions than the original.

Cause: Some OAuth servers require you to pass the original scope in the refresh request. If you omit scope or pass a different scope, the server may issue a token with default (reduced) scopes.

Fix: Store the original scope in a constant and pass it in both FinishLogin and Refresh token requests. If your connector supports user-configurable scopes, pass the scope through the Context field from StartLogin to FinishLogin, then store it... but wait, you can't store state between evaluations. For variable scopes, store them as a connector configuration parameter on the data source function, not as OAuth flow state.

---

Summary and Next Steps

You've built a production-grade authentication layer for a Power Query custom connector. Let's consolidate what you've implemented:

The architecture: Power Query separates credential definition (your M code) from credential storage (the host application). Your connector declares supported auth schemes, implements the OAuth protocol as four functions, and retrieves the current credential through Extension.CurrentCredential() at evaluation time.

The OAuth flow: StartLogin constructs the authorization URL and launches the login popup. FinishLogin exchanges the authorization code for tokens (using PKCE to prevent code interception). Refresh obtains new tokens when the access token expires. Logout revokes the session. The host orchestrates when each is called.

PKCE: Essential for public clients. Implemented using Binary.Random and Crypto.CreateHash with SHA-256, producing a Base64url-encoded verifier and challenge.

Multi-auth connectors: Declare multiple schemes in the Authentication record. Use Extension.CurrentCredential()[AuthenticationKind] as a discriminator in a centralized credential resolver function.

Security hygiene: ManualCredentials = true on all web calls, no credentials in error details, no credentials in URL query strings, careful handling of client secrets.

Next Steps

With this foundation, here's where to go deeper:

1. Connector certification: Microsoft's certification process for AppSource-published connectors adds requirements beyond what we've covered — specifically around CredentialScope, SSL certificate pinning assertions, and the TestConnection function. The SDK documentation on certified connectors is worth reading end-to-end.

2. Dynamic data source support: Some connectors need to generate data source paths dynamically (multi-tenant SaaS). Study DataSource.Path and the CredentialScope setting to understand how to control which connections share credentials.

3. Gateway deployment: Connectors deployed to on-premises data gateways have additional considerations — the gateway runs in a Windows service context, OAuth interactive flows aren't possible, and token refresh must be fully automatic. Test your connector in gateway mode early.

4. Table.View for query folding: Once authentication works, the next performance frontier is implementing Table.View with GetRows and OnTake/OnSkip/OnSelectColumns to push query operations to the API server rather than fetching all data into memory.

5. Connector signing: For distribution outside of your organization, Microsoft requires connectors to be signed with a trusted certificate. Set up a code signing pipeline using Azure Key Vault or a hardware token rather than a local certificate.

The credential infrastructure you've built today is reusable across every connector you'll ever write. The OAuth functions are largely boilerplate that you'll copy and adapt — the real connector-specific work is the data access layer that sits above them.