Building Custom Connectors in Power Query: Extending Data Source Support with the M SDK

Introduction

You've hit the wall that every serious Power Query developer eventually runs into: your organization's data lives in a system that Power Query doesn't natively support. Maybe it's a homegrown REST API that your engineering team built with a quirky authentication scheme. Maybe it's an industry-specific SaaS platform — a property management system, a clinical trial database, an IoT telemetry hub — that Microsoft has no plans to certify. Or perhaps you're an ISV who wants to ship a connector so your customers can pull data from your platform directly into Power BI without writing a single line of M themselves.

The answer to all of these problems is the same: build a custom connector using the Power Query M SDK. Custom connectors let you encapsulate connection logic, authentication handling, metadata discovery, and query folding hints into a single redistributable artifact — a .mez file — that behaves exactly like a first-party connector once installed. Done well, they eliminate the "get data from web" duct-tape workarounds that create credential management nightmares and break every time someone's API token expires.

By the end of this lesson, you will have built a working custom connector from scratch, understand how the M SDK's type system enforces connector contracts, and know how to handle OAuth 2.0 authentication, implement navigation tables, and package your connector for distribution. This is not a quickstart — we are going to get into the plumbing.

What you'll learn:

• How the Power Query SDK and the M extension model work under the hood
• How to structure a connector project using the .pq and .query.pq files
• How to implement OAuth 2.0 and API key authentication flows correctly
• How to build hierarchical navigation tables that expose your data cleanly
• How to enable query folding through schema inference and function metadata
• How to package, sign, and distribute a .mez connector file

---

Prerequisites

This lesson assumes you are comfortable writing M functions, understand how let...in expressions work, know what record types and table types are, and have previously used at least a few Power Query data sources. You should have Visual Studio Code installed. You do not need Visual Studio (the full IDE), but familiarity with command-line tooling helps.

You'll need to install:

• Visual Studio Code with the Power Query SDK extension (search "Power Query SDK" in the VS Code marketplace — it's published by Microsoft)
• .NET 8 SDK (the SDK's build toolchain requires it)
• The MakeMez tool, which the VS Code extension installs automatically as part of its initialization

---

Understanding the M Extension Model

Before you write a single line of code, you need to understand what a custom connector actually is inside the Power Query engine.

When Power Query loads a data source, it calls a function that returns either a table, a record, or a navigation table. First-party connectors — SQL Server, SharePoint, Salesforce — are implemented in exactly the same M language you use every day, just compiled into the engine itself. The M SDK gives you a sandboxed version of that same privilege. Your connector runs in the same evaluator, with almost the same capabilities, but in a separate trust boundary.

The fundamental artifact is a Data Source Kind — a record that declares your connector's identity, its authentication schemes, its display label, and a set of published functions. Here's the conceptual shape of that record:

DataSourceKind = [
    Authentication = [ ... ],
    Label = "My Custom Connector",
    Icons = [ ... ]
]

Your connector file (.pq) defines this record and then registers one or more Data Source Functions — M functions decorated with metadata that tells Power Query what to show in the Get Data dialog, how to invoke the function, and what type of thing it returns.

The key insight is that Power Query's extensibility model is entirely functional. There are no classes, no object instances, no lifecycle hooks. You write pure functions, decorate them with metadata records, and the engine does the rest. This is liberating once you internalize it, but it means the error messages are often cryptic when your metadata doesn't match what the engine expects.

The Sandbox and What It Restricts

Custom connectors run in a restricted environment by default. You cannot call arbitrary .NET libraries. You cannot write to disk. You cannot spawn processes. What you can do is call Web.Contents, Odbc.DataSource, OleDb.DataSource, AdoDotNet.DataSource, and a handful of other sanctioned I/O primitives. The engine gates these I/O calls specifically so that connector authors can't exfiltrate data or subvert Power Query's credential management.

When you're developing locally with the VS Code extension, you work in "extension development mode," which relaxes some of these restrictions. The EnableLoadFromCustomConnectorsFolder setting in Power BI Desktop opens a similar relaxed mode for unsigned connectors. Certified connectors (submitted to Microsoft for review) get a different trust level entirely — they can use Extension.LoadString for localization, access certain additional APIs, and appear in the standard Get Data dialog without the security warning.

Warning: Never enable unsigned custom connectors in a production Power BI tenant without a formal security review. The connector runs with the same permissions as the Power Query engine itself, which means it can make network calls with your cached credentials. Treat a .mez file the same way you'd treat a compiled executable.

---

Setting Up Your Development Environment

Create a new folder for your project. Open it in VS Code. Open the Command Palette (Ctrl+Shift+P) and run Power Query SDK: Create new project. Give it a name — let's call it FieldServiceConnector, because we're going to build a connector to a fictional field service management API.

The SDK scaffolds this directory structure:

FieldServiceConnector/
├── FieldServiceConnector.pq          # Main connector logic
├── FieldServiceConnector.query.pq    # Test harness expressions
├── FieldServiceConnector.proj        # Build file
└── resources/
    ├── FieldServiceConnector.png     # 16x16 icon
    ├── FieldServiceConnector20.png   # 20x20 icon
    ├── FieldServiceConnector24.png   # 24x24 icon
    └── FieldServiceConnector32.png   # 32x32 icon

The .pq file is where you write your connector. The .query.pq file is an M expression that the SDK evaluates against your connector during development — think of it as your integration test bed. The .proj file is a lightweight XML build descriptor.

Run Power Query SDK: Build project from the Command Palette. It compiles and packages everything into bin/AnyCPU/Debug/FieldServiceConnector.mez. If you copy that file to [My Documents]\Microsoft\Power BI Desktop\Custom Connectors\, Power BI Desktop will load it on next restart (assuming you've enabled the custom connector setting under Security options).

The Scaffolded Connector

The template generates a minimal connector. Let's look at what it produces and understand each part:

section FieldServiceConnector;

[DataSource.Kind="FieldServiceConnector", Publish="FieldServiceConnector.Publish"]
shared FieldServiceConnector.Contents = Value.ReplaceType(
    FieldServiceConnector.ContentsImpl,
    FieldServiceConnector.ContentsType
);

The section declaration names the M section — this is how the engine scopes identifiers within your connector. Everything in your .pq file lives in this section.

The [DataSource.Kind="FieldServiceConnector", Publish="FieldServiceConnector.Publish"] attribute tells the engine two things: which Data Source Kind record governs this function's credential management, and which Publish record controls how the function appears in the Get Data dialog.

The shared keyword makes this function visible outside the section — necessary for the engine to discover it.

Value.ReplaceType is a pattern you'll see constantly in connector development. It takes an implementation function and wraps it in a custom type that carries metadata. This separation exists because the M type system can't express UI labels, descriptions, and parameter ordering directly in a function signature — those live in the type annotation, and Value.ReplaceType is how you attach the annotated type to the unannotated implementation.

---

Defining Authentication

Authentication is where most connector projects get stuck. The M SDK supports several authentication kinds, and choosing the wrong one creates headaches that are hard to unwind.

For our FieldServiceConnector, let's say the API supports two modes: an API key passed as a header, and OAuth 2.0 with the authorization code flow. Defining both in the same connector is common for APIs that support both patterns depending on the client type.

API Key Authentication

API key authentication in Power Query is actually modeled as Key authentication — not UsernamePassword. The distinction matters: Key auth stores a single opaque token, while UsernamePassword stores a username/password pair. Here's how you declare it:

FieldServiceConnector = [
    Authentication = [
        Key = [
            KeyLabel = "API Key",
            Label = "Field Service API Key"
        ],
        OAuth = [
            StartLogin = FieldServiceConnector.StartLogin,
            FinishLogin = FieldServiceConnector.FinishLogin,
            Refresh = FieldServiceConnector.Refresh,
            Label = "OAuth 2.0"
        ]
    ],
    Label = "Field Service Manager",
    Icons = FieldServiceConnector.Icons
];

When using Key auth, Power Query stores the key in its credential vault and makes it available to your connector via Extension.CurrentCredential(). You access it like this:

FieldServiceConnector.GetAuthHeader = () =>
    let
        credential = Extension.CurrentCredential(),
        apiKey = credential[Key]
    in
        [#"X-API-Key" = apiKey];

Notice that you never see the key directly in your code at query time — Extension.CurrentCredential() hands you a record that contains the stored credential, and Power Query ensures this only works when the connector is being evaluated in the context of a legitimate query, not in a test expression that could leak it.

OAuth 2.0 Authorization Code Flow

OAuth is more involved. You need to implement three functions: StartLogin, FinishLogin, and optionally Refresh. Let's build these for a real-world authorization code flow.

// OAuth configuration constants
client_id = "your-client-id-here";
client_secret = "your-client-secret-here";
authorize_uri = "https://api.fieldservicemanager.io/oauth/authorize";
token_uri = "https://api.fieldservicemanager.io/oauth/token";
redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html";
scope = "read:work-orders read:technicians read:locations";

FieldServiceConnector.StartLogin = (resourceUrl, state, display) =>
    let
        authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
            client_id = client_id,
            redirect_uri = redirect_uri,
            state = state,
            scope = scope,
            response_type = "code"
        ])
    in
        [
            LoginUri = authorizeUrl,
            CallbackUri = redirect_uri,
            WindowHeight = 720,
            WindowWidth = 1024,
            Context = null
        ];

StartLogin is called when the user clicks "Sign In." It returns a record that tells Power Query where to open the OAuth browser window and what redirect URI to watch for. The state parameter is a CSRF token that Power Query generates — you must include it in your authorization URL, and you should validate it in FinishLogin.

FieldServiceConnector.FinishLogin = (context, callbackUri, state) =>
    let
        parts = Uri.Parts(callbackUri)[Query],
        code = parts[code],
        
        tokenResponse = Web.Contents(token_uri, [
            Content = Text.ToBinary(Uri.BuildQueryString([
                client_id = client_id,
                client_secret = client_secret,
                code = code,
                redirect_uri = redirect_uri,
                grant_type = "authorization_code"
            ])),
            Headers = [
                #"Content-Type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/json"
            ],
            ManualStatusHandling = {400, 401, 403}
        ]),
        
        tokenJson = Json.Document(tokenResponse),
        responseCode = Value.Metadata(tokenResponse)[Response.Status],
        
        result = if responseCode = 200 
            then tokenJson
            else error Error.Record(
                "Authentication.Error",
                "Token exchange failed: " & (tokenJson[error_description]? ?? tokenJson[error]? ?? "Unknown error"),
                tokenJson
            )
    in
        result;

Several things to notice here. First, Web.Contents inside FinishLogin uses ManualStatusHandling for error codes — without this, Power Query will throw a generic error on a 4xx response before you get a chance to read the error body and provide a meaningful message. Second, the ? operator after record field access (like tokenJson[error_description]?) returns null if the field doesn't exist rather than throwing — essential for handling API responses that vary their error schema. Third, Value.Metadata(tokenResponse)[Response.Status] is how you read the HTTP status code from a Web.Contents response.

FieldServiceConnector.Refresh = (resourceUrl, refresh_token) =>
    let
        tokenResponse = Web.Contents(token_uri, [
            Content = Text.ToBinary(Uri.BuildQueryString([
                client_id = client_id,
                client_secret = client_secret,
                refresh_token = refresh_token,
                grant_type = "refresh_token"
            ])),
            Headers = [
                #"Content-Type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/json"
            ],
            ManualStatusHandling = {400, 401}
        ]),
        tokenJson = Json.Document(tokenResponse)
    in
        tokenJson;

Tip: Store client_secret as a build-time constant only for development. For a production or certified connector, use a backend proxy that exchanges the authorization code server-side, so the client secret never ships in your .mez file. Anyone who unpacks your .mez (it's just a ZIP) can read string literals. The M SDK does not encrypt your source code.

---

Building the Data Retrieval Layer

Now let's build the actual connector logic. The field service API has three resource types: work orders, technicians, and service locations. Each supports pagination and filtering.

The Base Request Function

Every API call goes through a shared base function that handles authentication, pagination, and error normalization. Building this correctly from the start saves enormous pain later:

FieldServiceConnector.ApiRequest = (path as text, optional queryParams as record) as any =>
    let
        credential = Extension.CurrentCredential(),
        
        authHeader = if credential[AuthenticationKind] = "Key"
            then [#"X-API-Key" = credential[Key]]
            else [Authorization = "Bearer " & credential[access_token]],
        
        baseUrl = "https://api.fieldservicemanager.io/v2",
        
        url = baseUrl & path,
        
        options = [
            Headers = authHeader & [
                Accept = "application/json",
                #"X-Client-Version" = "1.0.0"
            ],
            ManualStatusHandling = {400, 401, 403, 404, 429, 500, 502, 503}
        ],
        
        fullOptions = if queryParams <> null 
            then options & [Query = Record.TransformFields(
                queryParams,
                List.Transform(
                    Record.FieldNames(queryParams),
                    (name) => {name, (val) => if val is number then Number.ToText(val) else val}
                )
            )]
            else options,
        
        response = Web.Contents(url, fullOptions),
        statusCode = Value.Metadata(response)[Response.Status],
        
        result = if statusCode = 200 
            then Json.Document(response)
            else if statusCode = 401
            then error Error.Record("DataSource.Error", "Authentication failed. Please refresh your credentials.", [HttpStatus = statusCode])
            else if statusCode = 429
            then error Error.Record("DataSource.Error", "Rate limit exceeded. Try again in a few minutes.", [HttpStatus = statusCode])
            else error Error.Record("DataSource.Error", 
                "API returned HTTP " & Number.ToText(statusCode), 
                [HttpStatus = statusCode, Body = Text.FromBinary(response)]
            )
    in
        result;

The Record.TransformFields call in the query parameter construction handles a subtle but important issue: Web.Contents expects query parameter values to be text, but callers will often pass numbers (like pageSize = 100). Rather than forcing every caller to do the conversion, the base function handles it.

Implementing Pagination

Most production APIs paginate their results. Our field service API uses cursor-based pagination — each response includes a nextCursor field, and you pass that cursor as a query parameter to get the next page. The M SDK has no built-in pagination primitive, so you implement it with List.Generate:

FieldServiceConnector.GetAllPages = (path as text, optional params as record) as list =>
    let
        firstPage = FieldServiceConnector.ApiRequest(path, params),
        
        allPages = List.Generate(
            () => [page = firstPage, cursor = firstPage[meta]?[nextCursor]?],
            (state) => state[page] <> null,
            (state) =>
                let
                    nextCursor = state[cursor],
                    nextParams = if nextCursor <> null 
                        then (if params = null then [] else params) & [cursor = nextCursor]
                        else null,
                    nextPage = if nextCursor <> null
                        then FieldServiceConnector.ApiRequest(path, nextParams)
                        else null
                in
                    [page = nextPage, cursor = if nextPage <> null then nextPage[meta]?[nextCursor]? else null],
            (state) => state[page][data]
        ),
        
        combinedData = List.Combine(allPages)
    in
        combinedData;

List.Generate takes four arguments: an initializer function, a condition function, a next-state function, and a selector function. The selector extracts just the data you care about from each state. The key to making this work correctly is being deliberate about the termination condition — if nextCursor is null, the next state's page field is null, which fails the condition on the next iteration, stopping generation.

Warning: Be careful with List.Generate and lazy evaluation. Power Query evaluates list elements lazily, which means all pages aren't fetched until something consumes the list. This is usually what you want, but it means errors from page 5 of 10 don't surface until that page is actually needed — which can make debugging confusing.

---

Building Navigation Tables

A navigation table is the mechanism by which a connector exposes multiple datasets through a hierarchical UI. When you click on a connector in Power Query and see a tree of databases, schemas, and tables — that's a navigation table. For our field service connector, we want to expose work orders, technicians, and locations as first-class tables.

Navigation tables are regular M tables with a specific set of columns and metadata attributes:

FieldServiceConnector.NavTable = (url as text) as table =>
    let
        objects = #table(
            {"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},
            {
                {
                    "Work Orders",
                    "WorkOrders",
                    FieldServiceConnector.WorkOrdersTable(url),
                    "Table",
                    "Table",
                    true
                },
                {
                    "Technicians",
                    "Technicians",
                    FieldServiceConnector.TechniciansTable(url),
                    "Table",
                    "Table",
                    true
                },
                {
                    "Service Locations",
                    "ServiceLocations",
                    FieldServiceConnector.ServiceLocationsTable(url),
                    "Table",
                    "Table",
                    true
                }
            }
        ),
        
        navTable = Table.ToNavigationTable(
            objects,
            {"Key"},
            "Name",
            "Data",
            "ItemKind",
            "ItemName",
            "IsLeaf"
        )
    in
        navTable;

Table.ToNavigationTable is a helper function that the SDK provides — it applies the required metadata to the table so Power Query's navigator dialog renders it correctly. The column roles are:

• Key: A unique identifier for each row — used by the navigator to handle selection
• Name: Display name shown in the navigator UI
• Data: The actual value — could be a table, another nav table, or any other M value
• ItemKind: Controls the icon displayed ("Table", "Database", "Schema", "View", "Function", "Folder")
• ItemName: Same as ItemKind in most cases, but can differ to customize the icon
• IsLeaf: true if this is a selectable data item, false if it's an intermediate folder node

For a nested hierarchy — say, work orders grouped by region — you'd set IsLeaf = false on folder nodes and put another nav table in the Data column.

Implementing the Individual Table Functions

Each table function fetches the data, applies a schema, and returns a typed table:

FieldServiceConnector.WorkOrdersTable = (url as text) as table =>
    let
        rawData = FieldServiceConnector.GetAllPages("/work-orders"),
        
        tableFromList = Table.FromList(
            rawData,
            Splitter.SplitByNothing(),
            {"Column1"}
        ),
        
        expanded = Table.ExpandRecordColumn(
            tableFromList,
            "Column1",
            {
                "id", "workOrderNumber", "status", "priority",
                "createdAt", "scheduledDate", "completedAt",
                "customerId", "customerName", "technicianId",
                "locationId", "description", "laborHours",
                "partsTotal", "totalCost"
            }
        ),
        
        typed = Table.TransformColumnTypes(expanded, {
            {"id", type text},
            {"workOrderNumber", type text},
            {"status", type text},
            {"priority", Int64.Type},
            {"createdAt", type datetimezone},
            {"scheduledDate", type datetimezone},
            {"completedAt", type datetimezone},
            {"customerId", type text},
            {"customerName", type text},
            {"technicianId", type text},
            {"locationId", type text},
            {"description", type text},
            {"laborHours", type number},
            {"partsTotal", type number},
            {"totalCost", type number}
        })
    in
        typed;

The column order in Table.ExpandRecordColumn matters from a usability standpoint — put the most important fields first, because that's what users see when they first load the data. The type assignments in Table.TransformColumnTypes are critical for downstream tools: without explicit types, Power BI's DAX engine treats everything as text and implicit conversions cause performance issues.

Tip: For datetimezone columns, ensure your API returns ISO 8601 strings with timezone information (e.g., 2024-03-15T09:30:00-05:00). If the API returns local time strings without timezone data, use DateTimeZone.LocalNow() to infer the offset — but document this assumption clearly, because it will be wrong for users in different time zones.

---

Implementing the Published Function and Type System

Now we wire everything together with the published function and its type annotation. This is the function users see in the Get Data dialog:

FieldServiceConnector.ContentsType = type function (
    url as (type text meta [
        Documentation.FieldCaption = "API Base URL",
        Documentation.FieldDescription = "The base URL of your Field Service Manager instance (e.g., https://yourcompany.fieldservicemanager.io)",
        Documentation.SampleValues = {"https://yourcompany.fieldservicemanager.io"}
    ]),
    optional options as (type record meta [
        Documentation.FieldCaption = "Options",
        Documentation.FieldDescription = "Optional configuration record"
    ])
) as table meta [
    Documentation.Name = "Field Service Manager",
    Documentation.LongDescription = "Connect to your Field Service Manager data to analyze work orders, technician performance, and service locations."
];

FieldServiceConnector.ContentsImpl = (url as text, optional options as record) as table =>
    FieldServiceConnector.NavTable(url);

[DataSource.Kind="FieldServiceConnector", Publish="FieldServiceConnector.Publish"]
shared FieldServiceConnector.Contents = Value.ReplaceType(
    FieldServiceConnector.ContentsImpl,
    FieldServiceConnector.ContentsType
);

The Documentation.* metadata fields are what populate the tooltips, labels, and sample values in the Power Query function invocation UI. This is the difference between a connector that feels professional and one that feels like a prototype. Take the time to write good descriptions.

The Publish Record

The Publish record controls the Get Data dialog entry:

FieldServiceConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = {"Field Service Manager", "Connect to your field service data"},
    LearnMoreUrl = "https://docs.yourcompany.io/powerbi",
    SourceImage = FieldServiceConnector.Icons,
    SourceTypeImage = FieldServiceConnector.Icons
];

FieldServiceConnector.Icons = [
    Icon16 = { Extension.Contents("FieldServiceConnector16.png") },
    Icon20 = { Extension.Contents("FieldServiceConnector20.png") },
    Icon24 = { Extension.Contents("FieldServiceConnector24.png") },
    Icon32 = { Extension.Contents("FieldServiceConnector32.png") }
];

Beta = true adds a "(Beta)" label to your connector in the Get Data dialog — appropriate during initial rollout. Set it to false once the connector is stable. Extension.Contents loads a file embedded in your .mez package by name — the file must exist in your project directory and be referenced in the .proj file for it to be bundled.

---

Advanced Pattern: Schema-Driven Column Handling

A common problem in connector development is API schema drift — the API adds new fields, and suddenly your Table.ExpandRecordColumn call drops them because they're not in your explicit list. The inverse problem is equally painful: you enumerate a field that the API has removed, and you get a column of nulls.

A more resilient pattern uses the actual response to drive column discovery, then enforces a schema on top:

FieldServiceConnector.ExpandAndEnforce = (rawTable as table, columnName as text, schema as table) as table =>
    let
        // Discover what fields actually exist in the first non-null record
        sampleRecord = List.First(
            List.RemoveNulls(Table.Column(rawTable, columnName))
        ),
        
        existingFields = if sampleRecord = null 
            then {} 
            else Record.FieldNames(sampleRecord),
        
        // Only expand fields that exist in both schema and response
        schemaFields = Table.Column(schema, "Name"),
        fieldsToExpand = List.Intersect({existingFields, schemaFields}),
        
        expanded = if List.Count(fieldsToExpand) > 0
            then Table.ExpandRecordColumn(rawTable, columnName, fieldsToExpand)
            else Table.RemoveColumns(rawTable, {columnName}),
        
        // Add missing schema columns as null
        missingFields = List.Difference(schemaFields, Table.ColumnNames(expanded)),
        withMissing = List.Accumulate(
            missingFields,
            expanded,
            (t, col) => Table.AddColumn(t, col, each null)
        ),
        
        // Apply types from schema
        typeMap = List.Transform(
            Table.ToRows(schema),
            (row) => {row{0}, row{1}}
        ),
        typed = Table.TransformColumnTypes(withMissing, typeMap)
    in
        typed;

This function takes your raw table (with a record column), a column name, and a schema table (with Name and Type columns). It discovers what actually exists in the response, expands only the intersection, adds nulled-out columns for anything missing, and applies types. It's more code, but it degrades gracefully when the API changes.

---

Query Folding Considerations

Query folding — where Power Query pushes transformation logic back to the data source — is mostly relevant for database connectors (SQL Server, PostgreSQL, etc.) that use Odbc.DataSource or Value.NativeQuery. For REST API connectors, full automatic folding isn't possible in the traditional sense, but you can implement a form of parameter folding that makes your connector significantly more efficient.

Parameter folding means that when a user filters on a column in Power Query, your connector's function can optionally detect that filter and translate it into an API query parameter — fetching only the relevant data instead of fetching everything and filtering in memory.

This requires using the Table.View function, which lets you override the behavior of standard table operations by providing custom handlers:

FieldServiceConnector.WorkOrdersTableWithFolding = (params as record) as table =>
    let
        baseTable = FieldServiceConnector.WorkOrdersTable(""),
        
        view = Table.View(baseTable, [
            GetType = () => Value.Type(baseTable),
            
            GetRows = () => FieldServiceConnector.WorkOrdersTable(""),
            
            OnSelectRows = (condition) =>
                let
                    // Attempt to extract simple equality conditions
                    // This is a simplified demonstration
                    foldedParams = FieldServiceConnector.TryFoldCondition(condition, params),
                    result = if foldedParams <> null
                        then FieldServiceConnector.WorkOrdersTableFromParams(foldedParams)
                        else Table.SelectRows(FieldServiceConnector.WorkOrdersTableFromParams(params), condition)
                in
                    result
        ])
    in
        view;

Table.View is one of the most powerful and least documented APIs in the M SDK. The OnSelectRows handler receives a condition function that represents the filter the user applied. You inspect that condition, and if you can translate it to an API parameter, you do so — otherwise, you fall back to fetching all data and filtering in memory. Fully implementing condition inspection requires pattern matching on the M row condition expression, which is an advanced topic worthy of its own lesson.

Tip: For most REST API connectors serving datasets under a few hundred thousand rows, don't bother with Table.View folding. The engineering cost is high and the benefit only materializes at scale. Implement it when users start reporting that connector queries time out or consume excessive memory.

---

Packaging and Distribution

Once your connector works correctly in development, you need to package it for distribution.

Building the Release MEZ

Run Power Query SDK: Build project in VS Code. The output is bin/AnyCPU/Debug/FieldServiceConnector.mez. For a release build, set the configuration to Release in the .proj file. A .mez file is a standard ZIP archive — you can verify its contents by renaming it to .zip and opening it.

Signing for Enterprise Distribution

If you're distributing your connector internally across a Power BI tenant, you have two options:

Option 1: Custom Connectors Folder Copy the .mez to [Documents]\Microsoft\Power BI Desktop\Custom Connectors\ on each user's machine. This works but doesn't scale and requires each user to enable the "(Not Recommended)" custom connector setting.

Option 2: Organizational Custom Connectors via Power BI Admin Portal If you're a Power BI tenant admin, you can whitelist specific connector files by hash in the Admin portal under "Custom Connectors." This allows end users to run specific connectors without enabling the global unrestricted setting.

Option 3: Certified Connector Submission If your connector is for a product used by many Power BI customers, you can submit it to Microsoft's connector certification program. Microsoft reviews the connector's security, tests it for compatibility, code-signs it, and includes it in future Power BI Desktop releases. This is a multi-month process but results in a first-class connector that requires no special settings to use.

What to Include in Your MEZ Package

Your .proj file controls what gets bundled. Make sure you include:

<ItemGroup>
  <None Include="FieldServiceConnector16.png" Pack="true" />
  <None Include="FieldServiceConnector20.png" Pack="true" />
  <None Include="FieldServiceConnector24.png" Pack="true" />
  <None Include="FieldServiceConnector32.png" Pack="true" />
</ItemGroup>

For localized connectors, you can include string resource files:

<None Include="resources\en-US\*.resx" Pack="true" />
<None Include="resources\de-DE\*.resx" Pack="true" />

And reference them using Extension.LoadString("ConnectorLabel") in your connector code — though this API is only available to certified connectors.

---

Hands-On Exercise

You're going to build a complete working connector to the GitHub REST API, which is publicly accessible and well-documented. This connector will expose your GitHub repositories and their commit history.

Step 1: Create the Project

Create a new VS Code workspace folder called GitHubConnector. Run Power Query SDK: Create new project and name it GitHubConnector.

Step 2: Set Up Authentication

GitHub's API supports both Personal Access Token (header-based) and OAuth. For this exercise, implement PAT authentication using the Key authentication kind. Your auth header should be Authorization: Bearer <token>.

Step 3: Implement the Base Request Function

Write GitHubConnector.ApiRequest that calls https://api.github.com and handles GitHub's specific requirements:

• Accept: application/vnd.github.v3+json header is required
• User-Agent header is required (GitHub rejects requests without it)
• Rate limit errors come back as HTTP 403 with a specific response body

Test your base function by calling /user and returning the authenticated user's profile.

Step 4: Build the Navigation Table

Create a navigation table with two top-level entries: My Repositories and Organization Repositories. My Repositories should fetch from /user/repos. For Organization Repositories, create a second level that lists organizations (from /user/orgs) and then shows repositories for the selected organization.

This nested navigation requires one nav table entry with IsLeaf = false and a Data value that is itself a nav table.

Step 5: Implement Pagination

GitHub uses Link header pagination — the response includes a Link header with URLs for the next page. However, since Web.Contents doesn't easily expose response headers, it's simpler to use GitHub's page and per_page query parameters. Implement List.Generate-based pagination that fetches 100 records per page and stops when a page returns fewer than 100 records.

Step 6: Apply Schema

Define an explicit schema for the repositories table. Include at minimum: id, name, full_name, description, language, stargazers_count, forks_count, open_issues_count, created_at, updated_at, pushed_at, visibility, html_url. Apply correct types — dates should be datetimezone.

Step 7: Test in Query.pq

In your .query.pq file, write test expressions that call each layer of your connector. Test the auth function independently, test pagination on a known public endpoint, and test the nav table structure.

Expected Challenge

You'll find that GitHub's API returns null for description on repositories that have none, and returns language = null for repositories with no detected language. Your Table.TransformColumnTypes call needs to handle nullable columns correctly. In M, type nullable text is different from type text — use the former for columns that can legitimately be null.

---

Common Mistakes & Troubleshooting

Mistake 1: Using `Web.Contents` with a dynamic URL

This is one of the most common and frustrating issues in connector development. Power Query's query folding and refresh infrastructure tracks which base URLs a connector accesses so it can associate credentials with the right data source. If you construct the full URL dynamically — including the path — Power Query can't determine at compile time where you're connecting to, and it may refuse to refresh the query in Power BI Service.

Wrong approach:

response = Web.Contents(baseUrl & "/work-orders?page=" & Number.ToText(page))

Correct approach:

response = Web.Contents(baseUrl, [
    RelativePath = "/work-orders",
    Query = [page = Number.ToText(page)]
])

Always pass the base URL as the first argument and use RelativePath and Query for the dynamic parts. The base URL must be a constant or come from a function parameter — never constructed at runtime.

Mistake 2: Forgetting ManualStatusHandling

When Web.Contents receives a non-2xx response, it throws an exception by default — and that exception's message is the generic HTTP status description, not your API's error body. You lose all the useful error information. Always pass ManualStatusHandling = {400, 401, 403, 404, 422, 429, 500, 502, 503} and handle each status code explicitly.

Mistake 3: The DataSource.Kind Mismatch

If the string in [DataSource.Kind="FieldServiceConnector"] doesn't exactly match the name of your Data Source Kind record, the engine silently fails to associate credentials with your connector. Everything appears to work in development (where the credential management is relaxed) but breaks in production. Always double-check this string. It's case-sensitive.

Mistake 4: Returning the Wrong Type from NavTable Data Column

If the Data column of your nav table contains a function reference instead of a called function, users will see a function in the navigator rather than data. This usually happens when you write:

{"My Table", "MyTable", MyConnector.MyTable, "Table", "Table", true}

instead of:

{"My Table", "MyTable", MyConnector.MyTable(), "Table", "Table", true}

The parentheses matter. Call the function; don't reference it.

Mistake 5: OAuth State Not Validated

If your FinishLogin function doesn't validate the state parameter returned in the callback URI against the state parameter passed to StartLogin, your connector is vulnerable to CSRF attacks. Always compare:

FinishLogin = (context, callbackUri, state) =>
    let
        callbackParts = Uri.Parts(callbackUri)[Query],
        returnedState = callbackParts[state]?,
        _ = if returnedState <> state 
            then error Error.Record("Security.Error", "State mismatch — possible CSRF attack", null)
            else null,
        code = callbackParts[code]
        // ... rest of token exchange
    in
        ...

Troubleshooting: Connector Doesn't Appear in Power BI Desktop

Check these in order: (1) The .mez file is in [Documents]\Microsoft\Power BI Desktop\Custom Connectors\ — create the folder if it doesn't exist. (2) Power BI Desktop's Security settings have "(Not Recommended)" custom connectors enabled. (3) You've restarted Power BI Desktop after copying the file. (4) The section name in your .pq file matches the filename (without extension). (5) Build the project fresh and check for compiler errors in the VS Code terminal.

Troubleshooting: Credentials Not Saved Between Sessions

This almost always means your DataSource.Kind record definition has a mistake, or the [DataSource.Kind="..."] attribute on your published function doesn't match the record name. Power Query uses this relationship to key the credential vault — if it's broken, no credentials are stored, and users are prompted every session.

---

Summary & Next Steps

You've covered an enormous amount of ground in this lesson. You understand the M extension model's architecture — the section system, the Data Source Kind record, the type annotation pattern with Value.ReplaceType. You've implemented both API key and OAuth 2.0 authentication, including the security considerations around state validation and client secret handling. You've built pagination with List.Generate, navigation tables with Table.ToNavigationTable, and resilient schema handling that degrades gracefully when APIs change. And you understand the connector distribution options from local development through enterprise rollout to Microsoft certification.

The skills you've built here compose with everything else in the Power Query ecosystem. A custom connector is ultimately just an M library — one that happens to be packaged and signed and trusted in a specific way. Once you have the packaging mechanics down, connector development is just M programming.

Where to go next:

• Query Folding with Table.View: Implementing OnSelectRows, OnTake, and OnSkip handlers to push filter and pagination logic to your API is the natural next step for high-performance connectors.

• ODBC-Based Connectors: If your data source supports ODBC, Odbc.DataSource gives you automatic query folding for almost all M table operations. The setup is different from REST connectors but the packaging is identical.

• Connector Certification: The Microsoft Power Query SDK documentation on GitHub (in the microsoft/DataConnectors repository) includes the certification checklist, sample connectors for reference, and the process for submitting to the Partner Center.

• DirectQuery Support: Some connector patterns support DirectQuery mode in Power BI, which keeps data in the source and translates DAX to API calls in real time. This is advanced territory that requires careful implementation of the Table.View folding API and specific metadata declarations.

• Testing Infrastructure: The .query.pq test harness is useful but limited. For production connectors, consider building a separate test suite using the MQuery.exe command-line evaluator that the SDK provides, which lets you run M expressions headlessly in CI/CD pipelines.

The field service connector you started in this lesson is production-ready in structure, if not in completeness. Fill in the remaining API endpoints, write your error messages carefully, and you have something real to ship.