Implementing Custom Connectors with M Language: Building, Packaging, and Deploying .mez Extensions for Enterprise Data Sources

There's a moment every Power BI developer eventually hits: you need to pull data from a system that Power BI doesn't support natively. Maybe it's a legacy ERP with a quirky REST API, an industry-specific SaaS platform that hasn't caught Microsoft's attention yet, or an internal data service your company built years ago. You poke around the "Get Data" dialog, find nothing useful, and start wondering if you're about to spend the next three weeks writing Python scripts to dump everything into CSVs first.

You don't have to. The Power Query SDK lets you build custom connectors — packaged .mez files that behave exactly like first-party connectors. Once deployed, your colleagues see your connector sitting right alongside the native SQL Server and Salesforce options. It authenticates properly, handles pagination, surfaces friendly error messages, and respects enterprise security requirements. Building one takes real M language knowledge, but it's entirely within reach of a practitioner who already understands how M works.

By the end of this lesson, you'll have built a complete, deployable .mez connector for a realistic HTTP API, structured it properly for enterprise use, and understood the full deployment pipeline from development machine to Power BI Gateway. Let's build something real.

What you'll learn:

• How .mez files are structured and what the Power Query SDK provides
• How to define authentication handlers, data source functions, and navigation tables
• How to implement robust pagination and error handling in M
• How to package and sign a connector for enterprise deployment
• How to deploy to both Power BI Desktop and the On-Premises Data Gateway

---

Prerequisites

You should already be comfortable with:

• M language fundamentals: functions, records, lists, tables, and the let...in pattern
• HTTP concepts: REST APIs, headers, authentication tokens, query parameters
• Power BI Desktop at a practitioner level
• Basic familiarity with Visual Studio Code

You'll need these tools installed:

• Power Query SDK for Visual Studio Code (the modern replacement for the older Visual Studio extension)
• Visual Studio Code (latest stable)
• .NET SDK 8.0 or later (required by the SDK build tooling)
• Power BI Desktop for testing

---

Understanding the .mez File Format

Before writing a single line of M, you need to understand what you're actually building. A .mez file is a renamed ZIP archive containing a specific set of files that Power Query's extension loading mechanism knows how to interpret. When Power Query loads your connector, it reads the manifest, registers your data source functions, and wires up authentication — all from these files.

A fully structured connector project looks like this:

MyConnector/
├── MyConnector.pq          # Main M code file
├── MyConnector.query.pq    # Test harness (not shipped)
├── MyConnector.png         # 16x16 icon
├── MyConnector@2x.png      # 32x32 icon (for high-DPI)
├── resources.resx          # Localized strings
└── .mezzanine/             # Build output (SDK-managed)

The .pq files are pure M language. The SDK compiles them into the binary format that Power Query's engine actually executes. Think of .pq files as your source code and the .mez as the compiled artifact — you never hand-edit a .mez directly.

The resources.resx file matters more than developers expect. Power Query uses it for the connector's display name, descriptions shown in the authentication dialog, and error message text. Hardcoding these strings into your M code instead of externalizing them to the resource file makes localization painful later and also prevents some enterprise governance checks from passing.

---

Setting Up the Development Environment

Install the Power Query SDK extension in VS Code. After installation, open the Command Palette (Ctrl+Shift+P) and run Power Query SDK: Create new project. Name the project WickedSmartInventory — we're going to build a connector for a fictional inventory management API that a lot of manufacturing companies actually have in various forms: a REST API sitting in front of an ERP system, requiring API key authentication, returning paginated JSON responses.

The SDK creates the boilerplate structure. Open WickedSmartInventory.pq and you'll see a minimal skeleton:

section WickedSmartInventory;

[DataSource.Kind="WickedSmartInventory", Publish="WickedSmartInventory.Publish"]
shared WickedSmartInventory.Contents = () => ...;

WickedSmartInventory = [
    Authentication = [
        Key = []
    ]
];

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

WickedSmartInventory.Icons = [
    Icon16 = { Extension.Contents("WickedSmartInventory16.png"), Extension.Contents("WickedSmartInventory20.png"), Extension.Contents("WickedSmartInventory24.png"), Extension.Contents("WickedSmartInventory32.png") },
    Icon32 = { Extension.Contents("WickedSmartInventory32.png"), Extension.Contents("WickedSmartInventory40.png"), Extension.Contents("WickedSmartInventory48.png"), Extension.Contents("WickedSmartInventory64.png") }
];

This is your blank canvas. Everything you write lives inside this section. Notice the section declaration at the top — this is an M concept most developers never encounter because regular Power Query doesn't use it. In a connector, every definition inside the section is scoped to the connector, and only values decorated with shared are exported as public functions that the Power Query engine can call.

---

Defining Authentication

Authentication is where most connector developers spend the most frustrating time, because getting it wrong means silent failures that look like network errors. Let's build API key authentication properly.

The Authentication record on your data source kind definition tells Power Query what credentials to collect. For API key auth:

WickedSmartInventory = [
    TestConnection = (dataSourcePath) =>
        { "WickedSmartInventory.Contents" },
    Authentication = [
        Key = [
            KeyLabel = Extension.LoadString("ApiKeyLabel"),
            Label = Extension.LoadString("ApiKeyDescription")
        ]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

The TestConnection field is critical for enterprise deployments. When IT configures the connector on the On-Premises Data Gateway, the gateway needs to verify the credentials actually work before storing them. TestConnection tells the gateway which function to call for that verification. It returns a list containing the function name and any arguments needed to invoke it. If your connector skips TestConnection, your IT team will have a bad time at gateway configuration.

When the user enters their API key, Power Query stores it in the credential store and makes it available during query execution via Extension.CurrentCredential(). Here's how you use it:

GetApiKey = () =>
    Extension.CurrentCredential()[Key];

You never pass the key as a function parameter — that would expose it in the query formula bar and potentially in logs. You always retrieve it from the credential store at call time.

For OAuth2, the Authentication record looks different, and you need to implement StartLogin, FinishLogin, and optionally Refresh functions. We'll stick with API key for this lesson, but the OAuth2 pattern follows the same principle: the SDK manages the credential lifecycle, you just implement the handlers.

---

Building the HTTP Request Layer

Now for the core of the connector: actually talking to the API. The inventory management API we're building against has these characteristics:

• Base URL: https://api.wickedsmart-inventory.com/v2
• Authentication: X-API-Key header
• Returns JSON with a data array and a pagination object
• Pagination uses cursor-based navigation: each response includes a nextCursor field

Start by building a low-level HTTP helper:

BaseUrl = "https://api.wickedsmart-inventory.com/v2";

MakeRequest = (path as text, optional queryParams as record) as binary =>
    let
        params = if queryParams = null then [] else queryParams,
        queryString = Uri.BuildQueryString(params),
        fullUrl = if Record.FieldCount(params) > 0
                  then BaseUrl & path & "?" & queryString
                  else BaseUrl & path,
        headers = [
            #"X-API-Key" = GetApiKey(),
            #"Accept" = "application/json",
            #"User-Agent" = "WickedSmartInventory-PowerQuery/1.0"
        ],
        response = Web.Contents(fullUrl, [
            Headers = headers,
            ManualStatusHandling = {400, 401, 403, 404, 429, 500, 503}
        ]),
        buffered = Binary.Buffer(response)
    in
        buffered;

Notice ManualStatusHandling. Without it, Web.Contents throws a generic error for any non-200 HTTP response, and you lose the response body that contains the API's actual error message. By listing status codes in ManualStatusHandling, you get control back and can surface meaningful error messages to the end user.

Also notice Binary.Buffer. This forces the HTTP response to be fully read into memory before we do anything else with it. Without buffering, M's lazy evaluation can cause the HTTP connection to be re-evaluated multiple times — you'd see multiple identical requests hitting your API, which breaks rate limiting and makes error handling unpredictable.

Now build the response handler that parses the response and checks for errors:

ParseResponse = (response as binary) as record =>
    let
        json = Json.Document(response),
        responseCode = Value.Metadata(response)[Response.Status],
        result = if responseCode = 200 then
            json
        else if responseCode = 401 then
            error Error.Record(
                "DataSource.Error",
                "Authentication failed. Check your API key.",
                [HttpStatus = responseCode]
            )
        else if responseCode = 429 then
            error Error.Record(
                "DataSource.Error",
                "Rate limit exceeded. Reduce refresh frequency or contact your API administrator.",
                [HttpStatus = responseCode, RetryAfter = Record.FieldOrDefault(json, "retryAfter", null)]
            )
        else
            error Error.Record(
                "DataSource.Error",
                "API error: " & Text.From(responseCode) & " - " & Record.FieldOrDefault(json, "message", "Unknown error"),
                [HttpStatus = responseCode, Details = json]
            )
    in
        result;

Warning: Always use Error.Record with "DataSource.Error" as the reason code for API-level failures. Using "Expression.Error" signals to Power Query that something is wrong with the formula, not the data source — which causes different (and confusing) retry behavior.

---

Implementing Cursor-Based Pagination

Pagination is where connector implementations most commonly fall apart. The naive approach — fetching all pages into a list and then combining — works fine for small datasets but causes memory exhaustion on anything enterprise-scale. The correct approach uses List.Generate to produce a lazy sequence of pages.

Here's a complete, production-grade paginated fetch implementation:

FetchAllPages = (path as text, optional queryParams as record) as list =>
    let
        initialParams = if queryParams = null then [] else queryParams,

        GetPage = (cursor as nullable text) as record =>
            let
                params = if cursor = null
                         then initialParams
                         else Record.AddField(initialParams, "cursor", cursor),
                rawResponse = MakeRequest(path, params),
                parsed = ParseResponse(rawResponse),
                data = parsed[data],
                nextCursor = Record.FieldOrDefault(parsed, "nextCursor", null),
                hasMore = nextCursor <> null and nextCursor <> ""
            in
                [Data = data, NextCursor = nextCursor, HasMore = hasMore],

        pages = List.Generate(
            () => GetPage(null),
            (state) => state[HasMore],
            (state) => GetPage(state[NextCursor]),
            (state) => state[Data]
        )
    in
        pages;

List.Generate takes four functions: an initializer, a condition, a next-state generator, and a selector. The selector is the key part — it extracts just the data you care about from each state record, so the accumulated list contains rows of data rather than accumulated state records.

Tip: The condition (state) => state[HasMore] runs before yielding the current state. This means the first page is always returned (it's produced by the initializer), and subsequent pages are returned as long as the previous page indicated there were more. This correctly handles the case where the very first response has no next cursor.

Now combine all pages into a single table:

GetTable = (path as text, optional queryParams as record) as table =>
    let
        allPages = FetchAllPages(path, queryParams),
        combined = List.Combine(allPages),
        table = Table.FromList(
            combined,
            Splitter.SplitByNothing(),
            null,
            null,
            ExtraValues.Error
        ),
        expanded = Table.ExpandRecordColumn(
            table,
            "Column1",
            Record.FieldNames(table{0}[Column1])
        )
    in
        expanded;

The Table.ExpandRecordColumn call dynamically reads field names from the first record. This is pragmatic but has a gotcha: if the first record is missing optional fields that appear in later records, those columns will be absent. For production connectors serving enterprise data, consider defining a static schema instead.

---

Defining the Navigation Table

Power BI Desktop uses a navigation table to display your connector's data sources in the familiar folder/table hierarchy. Without one, users get a single undifferentiated blob of data. With one, they see "Inventory", "Suppliers", "Purchase Orders" as selectable tables — exactly like the SQL Server connector shows databases and tables.

shared WickedSmartInventory.Contents = () as table =>
    let
        navTable = #table(
            {"Name", "Data", "ItemKind", "ItemName", "IsLeaf"},
            {
                {
                    "Inventory Items",
                    GetTable("/items", [pageSize = "500"]),
                    "Table",
                    "Table",
                    true
                },
                {
                    "Suppliers",
                    GetTable("/suppliers", [pageSize = "200"]),
                    "Table",
                    "Table",
                    true
                },
                {
                    "Purchase Orders",
                    GetTable("/purchase-orders", [pageSize = "100"]),
                    "Table",
                    "Table",
                    true
                },
                {
                    "Locations",
                    GetTable("/locations"),
                    "Table",
                    "Table",
                    true
                }
            }
        ),
        navTableWithMetadata = Table.ToNavigationTable(
            navTable,
            {"Name"},
            "Name",
            "Data",
            "ItemKind",
            "ItemName",
            "IsLeaf"
        )
    in
        navTableWithMetadata;

Table.ToNavigationTable is an M standard library function that stamps the correct metadata onto the table so Power Query renders it as a navigator. The column names in the #table definition must match exactly what you pass to Table.ToNavigationTable — case-sensitively. Getting this wrong produces a navigation table that looks right but doesn't navigate.

Tip: Set IsLeaf = true for tables (leaf nodes the user can load) and IsLeaf = false for folders. If all your sources are tables with no grouping needed, set everything to true.

---

Static Schema Enforcement

For enterprise deployments, raw dynamic typing is risky. If the upstream API adds a field, changes a data type, or returns an empty first page, your Power BI dataset schema drifts — and report columns silently break. The solution is to define a schema table and enforce it explicitly.

InventoryItemsSchema = #table(
    {"Name", "Type"},
    {
        {"itemId", type text},
        {"sku", type text},
        {"description", type text},
        {"quantityOnHand", Int64.Type},
        {"quantityReserved", Int64.Type},
        {"unitCost", Currency.Type},
        {"supplierId", type text},
        {"lastUpdated", type datetimezone},
        {"isActive", type logical}
    }
);

EnforceSchema = (table as table, schema as table) as table =>
    let
        schemaFieldNames = schema[Name],
        tableWithOnlySchemaColumns = Table.SelectColumns(
            table,
            schemaFieldNames,
            MissingField.UseNull
        ),
        typedTable = Table.TransformColumnTypes(
            tableWithOnlySchemaColumns,
            Table.ToRows(Table.SelectColumns(schema, {"Name", "Type"}))
        )
    in
        typedTable;

The MissingField.UseNull parameter to Table.SelectColumns is the safety valve: if the API stops returning a column, the connector fills it with nulls rather than throwing an error. This prevents report breakage during upstream API changes, giving you time to react deliberately rather than having your operations team page you at 2am.

Apply schema enforcement in the data retrieval function:

GetInventoryItems = () as table =>
    let
        rawTable = GetTable("/items", [pageSize = "500"]),
        enforcedTable = EnforceSchema(rawTable, InventoryItemsSchema)
    in
        enforcedTable;

Then update the navigation table to reference GetInventoryItems() instead of the raw GetTable call.

---

Building the Complete Connector File

Here's the full WickedSmartInventory.pq assembled with everything we've covered:

section WickedSmartInventory;

// ─── Configuration ────────────────────────────────────────────────────────────

BaseUrl = "https://api.wickedsmart-inventory.com/v2";

// ─── Authentication ───────────────────────────────────────────────────────────

GetApiKey = () => Extension.CurrentCredential()[Key];

// ─── Data Source Definition ───────────────────────────────────────────────────

WickedSmartInventory = [
    TestConnection = (dataSourcePath) => { "WickedSmartInventory.Contents" },
    Authentication = [
        Key = [
            KeyLabel = Extension.LoadString("ApiKeyLabel"),
            Label = Extension.LoadString("ApiKeyDescription")
        ]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

WickedSmartInventory.Publish = [
    Beta = false,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    LearnMoreUrl = "https://docs.wickedsmart-inventory.com/powerbi",
    SourceImage = WickedSmartInventory.Icons,
    SourceTypeImage = WickedSmartInventory.Icons
];

WickedSmartInventory.Icons = [
    Icon16 = { Extension.Contents("WickedSmartInventory16.png") },
    Icon32 = { Extension.Contents("WickedSmartInventory32.png") }
];

// ─── HTTP Layer ───────────────────────────────────────────────────────────────

MakeRequest = (path as text, optional queryParams as record) as binary =>
    let
        params = if queryParams = null then [] else queryParams,
        queryString = Uri.BuildQueryString(params),
        fullUrl = if Record.FieldCount(params) > 0
                  then BaseUrl & path & "?" & queryString
                  else BaseUrl & path,
        headers = [
            #"X-API-Key" = GetApiKey(),
            #"Accept" = "application/json",
            #"User-Agent" = "WickedSmartInventory-PowerQuery/1.0"
        ],
        response = Web.Contents(fullUrl, [
            Headers = headers,
            ManualStatusHandling = {400, 401, 403, 404, 429, 500, 503}
        ]),
        buffered = Binary.Buffer(response)
    in
        buffered;

ParseResponse = (response as binary) as record =>
    let
        json = Json.Document(response),
        responseCode = Value.Metadata(response)[Response.Status],
        result = if responseCode = 200 then json
                 else if responseCode = 401 then
                     error Error.Record("DataSource.Error",
                         "Authentication failed. Verify your API key.", [HttpStatus = 401])
                 else if responseCode = 429 then
                     error Error.Record("DataSource.Error",
                         "Rate limit exceeded. Reduce refresh frequency.", [HttpStatus = 429])
                 else
                     error Error.Record("DataSource.Error",
                         "API error " & Text.From(responseCode) & ": " &
                         Record.FieldOrDefault(json, "message", "Unknown error"),
                         [HttpStatus = responseCode])
    in
        result;

// ─── Pagination ───────────────────────────────────────────────────────────────

FetchAllPages = (path as text, optional queryParams as record) as list =>
    let
        initialParams = if queryParams = null then [] else queryParams,
        GetPage = (cursor as nullable text) as record =>
            let
                params = if cursor = null then initialParams
                         else Record.AddField(initialParams, "cursor", cursor),
                parsed = ParseResponse(MakeRequest(path, params))
            in
                [
                    Data = parsed[data],
                    NextCursor = Record.FieldOrDefault(parsed, "nextCursor", null),
                    HasMore = Record.FieldOrDefault(parsed, "nextCursor", null) <> null
                ],
        pages = List.Generate(
            () => GetPage(null),
            (state) => state[HasMore],
            (state) => GetPage(state[NextCursor]),
            (state) => state[Data]
        )
    in
        pages;

GetTable = (path as text, optional queryParams as record) as table =>
    let
        allPages = FetchAllPages(path, queryParams),
        combined = List.Combine(allPages),
        table = Table.FromList(combined, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        firstRecord = table{0}[Column1],
        expanded = Table.ExpandRecordColumn(table, "Column1", Record.FieldNames(firstRecord))
    in
        expanded;

// ─── Schema Definitions ───────────────────────────────────────────────────────

InventoryItemsSchema = #table({"Name", "Type"}, {
    {"itemId",          type text},
    {"sku",             type text},
    {"description",     type text},
    {"quantityOnHand",  Int64.Type},
    {"quantityReserved",Int64.Type},
    {"unitCost",        Currency.Type},
    {"supplierId",      type text},
    {"lastUpdated",     type datetimezone},
    {"isActive",        type logical}
});

SuppliersSchema = #table({"Name", "Type"}, {
    {"supplierId",   type text},
    {"supplierName", type text},
    {"country",      type text},
    {"leadTimeDays", Int64.Type},
    {"isPreferred",  type logical}
});

EnforceSchema = (tbl as table, schema as table) as table =>
    let
        schemaFieldNames = schema[Name],
        selected = Table.SelectColumns(tbl, schemaFieldNames, MissingField.UseNull),
        typed = Table.TransformColumnTypes(
            selected,
            Table.ToRows(Table.SelectColumns(schema, {"Name", "Type"}))
        )
    in
        typed;

// ─── Data Fetching Functions ──────────────────────────────────────────────────

GetInventoryItems = () as table => EnforceSchema(GetTable("/items", [pageSize="500"]), InventoryItemsSchema);
GetSuppliers = () as table => EnforceSchema(GetTable("/suppliers", [pageSize="200"]), SuppliersSchema);
GetPurchaseOrders = () as table => GetTable("/purchase-orders", [pageSize="100"]);
GetLocations = () as table => GetTable("/locations");

// ─── Navigation Table ─────────────────────────────────────────────────────────

[DataSource.Kind="WickedSmartInventory", Publish="WickedSmartInventory.Publish"]
shared WickedSmartInventory.Contents = () as table =>
    let
        navTable = #table(
            {"Name", "Data", "ItemKind", "ItemName", "IsLeaf"},
            {
                {"Inventory Items",  GetInventoryItems(),   "Table", "Table", true},
                {"Suppliers",        GetSuppliers(),        "Table", "Table", true},
                {"Purchase Orders",  GetPurchaseOrders(),   "Table", "Table", true},
                {"Locations",        GetLocations(),        "Table", "Table", true}
            }
        )
    in
        Table.ToNavigationTable(navTable, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf");

---

Packaging and Signing the .mez File

With the code complete, build the connector using the Power Query SDK. In VS Code, open the Command Palette and run Power Query SDK: Build connector project. This produces a .mez file in the bin/AnyCPU/Debug/ folder (or Release/ if you built in release mode).

For internal testing, the unsigned .mez is sufficient. For enterprise distribution, you have two options:

Option 1: Self-signed certificate (for internal corporate use)

# Generate a self-signed cert (run in PowerShell as Administrator)
$cert = New-SelfSignedCertificate `
    -DnsName "WickedSmartInventory-Connector" `
    -CertStoreLocation "cert:\LocalMachine\My" `
    -KeyUsage DigitalSignature `
    -Type CodeSigningCert

# Sign the .mez file
$mez = ".\bin\AnyCPU\Release\WickedSmartInventory.mez"
Set-AuthenticodeSignature -FilePath $mez -Certificate $cert

Option 2: Commercial code signing certificate

Use your organization's code signing certificate with signtool.exe from the Windows SDK:

signtool sign `
    /sha1 <certificate-thumbprint> `
    /tr http://timestamp.digicert.com `
    /td sha256 `
    /fd sha256 `
    .\bin\AnyCPU\Release\WickedSmartInventory.mez

Signing matters beyond security theater. Power BI Desktop and the gateway check signatures to decide how much trust to grant the connector. An unsigned connector can only run with the "Allow any extension to load without validation" setting enabled — which security-conscious IT departments won't permit.

---

Deploying to Power BI Desktop and the Gateway

Desktop deployment is simple: copy the .mez file to [Documents]\Power BI Desktop\Custom Connectors\. Restart Power BI Desktop, go to File > Options and Settings > Options > Security, and set the data extension security to "Allow loading of uncertified connectors" if you're using an internal self-signed certificate. After restart, your connector appears in the "Get Data" dialog under "Other."

Gateway deployment is what actually matters for enterprise scheduled refresh:

1. Copy the .mez to the Custom Connectors folder on each gateway machine. The default path is C:\Users\PBIEgwService\Documents\Power BI Desktop\Custom Connectors\. Note the service account — it's the gateway service user, not your personal profile.

2. Open the On-Premises Data Gateway app on the gateway machine, go to Connectors, and confirm your connector appears in the list of detected custom connectors.

3. Restart the gateway service from the gateway app (or via Windows Services) after adding the connector.

4. In the Power BI Service, go to your dataset's settings, expand "Gateway connection," and confirm the gateway can see and use your connector.

Warning: If you update the .mez file, you must restart the gateway service for the new version to load. The gateway caches connectors at startup. Forgetting this step causes confusing situations where you've fixed a bug but the old behavior persists.

For large enterprises with multiple gateways, consider automating the deployment with a simple PowerShell script that copies the file and restarts the service across all gateway machines via Invoke-Command. This prevents version skew between gateway nodes.

---

Hands-On Exercise

Build a connector for the JSONPlaceholder API (https://jsonplaceholder.typicode.com) — a free, public REST API that returns realistic fake data. This lets you build a complete connector without needing API credentials.

Your connector should:

1. Use Anonymous authentication (since JSONPlaceholder requires none)
2. Expose three tables: Posts (/posts), Users (/users), and Comments (/comments)
3. Enforce a static schema for the Posts table with at least these fields: id (Int64), userId (Int64), title (text), body (text)
4. Handle HTTP errors gracefully with ManualStatusHandling
5. Provide a proper navigation table

Starting point for Anonymous authentication:

MyConnector = [
    TestConnection = (dataSourcePath) => { "MyConnector.Contents" },
    Authentication = [
        Anonymous = []
    ],
    Label = "JSONPlaceholder Demo"
];

Stretch goal: JSONPlaceholder doesn't actually paginate, but simulate a pagination implementation by adding support for a _page and _limit query parameter (JSONPlaceholder does honor these) and writing a FetchAllPages function that stops when a page returns fewer results than the page size.

Build it, get it working in Power BI Desktop, then try connecting to one of the tables and loading the data. If you can load Posts with correct data types, you've succeeded.

---

Common Mistakes and Troubleshooting

"Formula.Firewall: Query references other queries" error

This happens when Power Query's firewall detects that two queries with different data source contexts are being combined. In a connector context, it usually means you're combining data from two different URL paths and Power Query is treating them as separate data sources. Fix: ensure all requests share the same base URL, and declare your data source kind's Publish record with SupportsDirectQuery = false if you're not implementing DirectQuery. In some cases you may need DataSource.Path to be set to just the base domain.

Connector doesn't appear in "Get Data"

Check: Is the .mez file in exactly the right folder? Is Power BI Desktop configured to allow uncertified connectors? Is the file actually a valid ZIP (try renaming it to .zip and opening it — if it fails, the build produced a corrupt file)? Restart Power BI Desktop completely.

Extension.CurrentCredential() returns null

This means Power Query doesn't have credentials stored for your data source. During development in the SDK test harness, set credentials via Power Query SDK: Set credential. In Power BI Desktop, you'll be prompted on first connection — but only if the Authentication record is correctly defined. If it's malformed, Desktop silently skips the credential prompt.

Pagination loops infinitely

Your HasMore condition is always evaluating to true. Common cause: the API returns nextCursor: "" (empty string) for the last page instead of omitting the field or returning null. Fix: check both conditions — nextCursor <> null AND nextCursor <> "".

Data loads in Desktop but gateway refresh fails with "DataSource.Error"

Credentials stored in Desktop aren't available on the gateway. Re-enter credentials via the Power BI Service gateway settings page. Also confirm the gateway service account has network access to the API endpoint — corporate firewalls often block the service account while allowing your user account.

Schema enforcement fails with "Column not found"

Table.SelectColumns with MissingField.UseNull should prevent this, but if you're using MissingField.Error (the default), missing columns throw. Also check that Record.FieldNames(firstRecord) is returning the right field names — if the first API response row is unexpectedly empty or structured differently, the expansion step breaks.

---

Summary and Next Steps

You've built a complete, enterprise-grade Power Query custom connector: an HTTP layer with proper buffering and error handling, cursor-based pagination that won't exhaust memory on large datasets, static schema enforcement that survives upstream API changes, a properly structured navigation table, and a deployment pipeline that covers both Desktop and the On-Premises Data Gateway.

The patterns here — ManualStatusHandling, Binary.Buffer, List.Generate for pagination, schema tables, TestConnection for gateway validation — aren't just good practices for connectors. They represent mature M language thinking that applies to complex Power Query transformations generally.

Where to go from here:

• Implement OAuth2 authentication. The pattern involves StartLogin, FinishLogin, and Refresh functions, and is necessary for connectors targeting any modern SaaS platform. The Power Query SDK documentation covers the OAuth2 flow with a template you can adapt.

• Add DirectQuery support. For high-volume data sources where import mode is impractical, you can implement SqlCapabilities and translation from M query predicates to API filter parameters. It's significantly more complex but the right architecture for real-time operational data.

• Explore query folding hints. Even without full DirectQuery, you can use Table.View to implement server-side filtering for specific operations — so when a user filters by date range in Power BI, your connector translates that into API query parameters rather than pulling all data and filtering client-side.

• Publish to the Power BI connector ecosystem. If your connector serves data that other organizations could use, Microsoft's certification program can get it listed as a first-party connector. The bar is high — security review, documentation requirements, ongoing maintenance commitment — but it's worth knowing the path exists.

The .mez ecosystem is one of the most underutilized parts of the Power BI platform. Most organizations are still copying data into staging tables when a well-built custom connector would serve it directly, with proper authentication, in real time. Now you know how to build one.