Implementing Custom OData Feed Clients in Power Query M: Handling Service Metadata, Entity Expansion, and Filter Pushdown for Enterprise API Integration

Introduction

Your enterprise ERP just got upgraded, and the vendor has exposed a shiny new OData v4 API surface. The built-in OData.Feed connector in Power Query works for simple cases, but three days in you hit the wall: navigation property expansion isn't folding, your filter for OrderDate ge 2024-01-01 is being ignored and all 4 million rows are landing in memory before your date filter runs, and the $metadata document is a 12,000-line XML behemoth that the connector is fetching on every single refresh. You need a solution that behaves more like a real OData client — one that understands the service contract, pushes filter predicates to the server, expands related entities in a single round trip, and handles the quirks of whatever semi-standard implementation your vendor shipped.

This lesson walks you through building exactly that. We'll implement a custom OData feed client in M that reads and caches service metadata, constructs $expand, $filter, $select, and $orderby query options programmatically, and gives you enough control to handle the real-world divergences from the OData specification that enterprise APIs always seem to introduce. We'll use a realistic scenario throughout: pulling sales orders with related customer and product line items from a SAP Business Central OData v4 endpoint.

What you'll learn:

• How to parse an OData $metadata document in M to extract entity types, navigation properties, and key fields
• How to programmatically construct OData system query options ($filter, $expand, $select, $orderby, $top, $skip) and compose them into well-formed URLs
• How to implement filter pushdown so date ranges, text predicates, and enum comparisons are evaluated on the server — not in Power Query memory
• How to handle server-side pagination through @odata.nextLink continuations and merge the results correctly
• How to cache metadata responses and structure your queries to maximize query folding opportunities

---

Prerequisites

You should be comfortable with M's type system, record and list manipulation, and custom function authoring before diving in. If you need a refresher, M Language Fundamentals: Syntax, Types, and Expressions for Power Query covers the core concepts, and List and Record Operations in M: Transform, Select, and Combine Data Structures will sharpen your skills with the data structures we'll use heavily here. You should also understand how query folding works at a conceptual level — Implementing Custom Query Folding Logic in M: Keeping Transformations Native to the Data Source is essential reading before this lesson.

---

Understanding OData as a Protocol, Not Just a URL Pattern

Before we write a single line of M, it's worth getting precise about what OData actually is, because most Power Query users treat it as "REST but with dollar signs." That mental model will hurt you at the expert level.

OData is a full protocol built on HTTP and AtomPub (v3) or JSON (v4) that defines not just URL conventions but a type system (the Entity Data Model), a service document (/), a metadata document (/$metadata), and a set of system query options that are supposed to be understood and partially evaluated by the server before data is returned. The key phrase is supposed to be. In practice, every enterprise OData implementation you'll encounter makes different choices about which query options they actually push down to their data store, which ones they silently ignore, and which ones they accept syntactically but evaluate in their own middleware layer.

The built-in OData.Feed function in Power Query tries to be smart about this. It fetches $metadata, builds an internal schema, and attempts query folding when you apply filters through the Power Query UI. But it's a general-purpose connector designed for the common case. When you're dealing with Business Central's OData v4 API, Dynamics 365's complex navigation hierarchies, or a custom-built OData service running on an enterprise middleware platform, you'll need custom handling for:

• Metadata endpoints that require authentication headers before they'll respond
• Entity sets with tens of navigation properties where you only want to expand two
• Filter expressions that require server-specific function syntax (like Microsoft.NAV.today() instead of standard now())
• nextLink pagination that uses different URL encoding than the base endpoint
• Services that return 200 OK with an OData error JSON body instead of using HTTP error codes

Building your own client gives you the control to handle all of this explicitly.

---

Step 1: Fetching and Parsing the $metadata Document

The OData $metadata document is an XML file conforming to the CSDL (Common Schema Definition Language) specification. It describes every entity type, complex type, enum type, entity set, and navigation property available on the service. For our Business Central example, the base URL would be something like:

https://api.businesscentral.dynamics.com/v2.0/{tenantId}/{environment}/api/v2.0/$metadata

Let's build a function that fetches this document, parses it, and returns a structured record we can query programmatically. We'll use Advanced JSON and XML Processing in Power Query M Language techniques for the XML parsing.

let
    FetchServiceMetadata = (baseUrl as text, optional headers as record) as record =>
    let
        // Merge in default OData headers with any caller-supplied headers
        DefaultHeaders = [
            #"Accept" = "application/xml",
            #"OData-MaxVersion" = "4.0",
            #"OData-Version" = "4.0"
        ],
        MergedHeaders = Record.Combine({DefaultHeaders, headers ?? []}),

        // Fetch the raw metadata XML
        MetadataUrl = baseUrl & "/$metadata",
        RawResponse = Web.Contents(
            MetadataUrl,
            [
                Headers = MergedHeaders,
                ManualStatusHandling = {400, 401, 403, 404, 500}
            ]
        ),
        ResponseCode = Value.Metadata(RawResponse)[Response.Status],
        _ = if ResponseCode <> 200
            then error Error.Record(
                "OData.MetadataError",
                "Metadata endpoint returned HTTP " & Number.ToText(ResponseCode),
                [Url = MetadataUrl, StatusCode = ResponseCode]
            )
            else null,

        // Parse XML into navigable structure
        MetadataXml = Xml.Document(RawResponse),

        // Navigate into the Schema element
        // OData v4 CSDL namespace
        CsdlNs = "http://docs.oasis-open.org/odata/ns/edm",
        Edmx = MetadataXml{[Name = "Edmx"]}[Children],
        DataServices = Edmx{[Name = "DataServices"]}[Children],
        Schema = DataServices{[Name = "Schema"]}[Children],

        // Extract all EntityType definitions
        EntityTypes = List.Select(Schema, each _[Name] = "EntityType"),
        EntityTypeMap = Record.FromList(
            List.Transform(
                EntityTypes,
                each ParseEntityType(_)
            ),
            List.Transform(EntityTypes, each _[Attributes][Name])
        ),

        // Extract EntityContainer (entity sets and singletons)
        ContainerElements = List.Select(Schema, each _[Name] = "EntityContainer"),
        EntityContainer = if List.Count(ContainerElements) > 0
            then ContainerElements{0}[Children]
            else {},
        EntitySets = List.Select(EntityContainer, each _[Name] = "EntitySet"),
        EntitySetMap = Record.FromList(
            List.Transform(EntitySets, each [
                EntityType = _[Attributes][EntityType],
                Name = _[Attributes][Name]
            ]),
            List.Transform(EntitySets, each _[Attributes][Name])
        )
    in
        [
            EntityTypes = EntityTypeMap,
            EntitySets = EntitySetMap,
            BaseUrl = baseUrl
        ]
in
    FetchServiceMetadata

Now let's build the ParseEntityType helper that extracts properties and navigation properties from a single entity type XML node:

let
    ParseEntityType = (entityTypeNode as record) as record =>
    let
        Children = entityTypeNode[Children],
        Attributes = entityTypeNode[Attributes],

        // Key properties
        KeyElements = List.Select(Children, each _[Name] = "Key"),
        KeyPropertyRefs = if List.Count(KeyElements) > 0
            then List.Transform(
                KeyElements{0}[Children],
                each _[Attributes][Name]
            )
            else {},

        // Scalar properties
        PropertyElements = List.Select(Children, each _[Name] = "Property"),
        Properties = Record.FromList(
            List.Transform(PropertyElements, each [
                Type = _[Attributes][Type],
                Nullable = Record.FieldOrDefault(_[Attributes], "Nullable", "true") = "true",
                MaxLength = Record.FieldOrDefault(_[Attributes], "MaxLength", null)
            ]),
            List.Transform(PropertyElements, each _[Attributes][Name])
        ),

        // Navigation properties (relationships to other entity types)
        NavPropertyElements = List.Select(Children, each _[Name] = "NavigationProperty"),
        NavigationProperties = Record.FromList(
            List.Transform(NavPropertyElements, each [
                Type = _[Attributes][Type],
                Partner = Record.FieldOrDefault(_[Attributes], "Partner", null),
                IsCollection = Text.StartsWith(_[Attributes][Type], "Collection(")
            ]),
            List.Transform(NavPropertyElements, each _[Attributes][Name])
        )
    in
        [
            Name = Attributes[Name],
            Keys = KeyPropertyRefs,
            Properties = Properties,
            NavigationProperties = NavigationProperties
        ]
in
    ParseEntityType

The output of FetchServiceMetadata gives us a structured record we can query to answer questions like "what are the navigation properties of salesOrders?" and "which properties are nullable?" before we construct a single data request.

---

Step 2: Building the Query Option Constructor

With metadata in hand, we can build an intelligent URL constructor. OData system query options combine into a query string, but the order matters for readability, the encoding matters for correctness, and some servers are particular about how you format filter expressions.

Let's build a BuildODataUrl function that takes a record of options and produces a properly encoded URL:

let
    BuildODataUrl = (
        baseUrl as text,
        entitySet as text,
        optional options as record
    ) as text =>
    let
        Opts = options ?? [],

        // $select: list of property names
        SelectClause = if Record.HasFields(Opts, "$select") and List.Count(Opts[#"$select"]) > 0
            then "$select=" & Text.Combine(Opts[#"$select"], ",")
            else null,

        // $expand: either a simple list or a list of records with nested options
        ExpandClause = if Record.HasFields(Opts, "$expand")
            then "$expand=" & BuildExpandClause(Opts[#"$expand"])
            else null,

        // $filter: a pre-built OData filter string
        FilterClause = if Record.HasFields(Opts, "$filter") and Opts[#"$filter"] <> null
            then "$filter=" & Uri.EscapeDataString(Opts[#"$filter"])
            else null,

        // $orderby: list of "Property asc/desc" strings
        OrderByClause = if Record.HasFields(Opts, "$orderby") and List.Count(Opts[#"$orderby"]) > 0
            then "$orderby=" & Text.Combine(Opts[#"$orderby"], ",")
            else null,

        // $top: integer
        TopClause = if Record.HasFields(Opts, "$top") and Opts[#"$top"] <> null
            then "$top=" & Number.ToText(Opts[#"$top"])
            else null,

        // $skip: integer
        SkipClause = if Record.HasFields(Opts, "$skip") and Opts[#"$skip"] <> null
            then "$skip=" & Number.ToText(Opts[#"$skip"])
            else null,

        // $count: boolean
        CountClause = if Record.HasFields(Opts, "$count") and Opts[#"$count"] = true
            then "$count=true"
            else null,

        // Assemble all non-null clauses
        AllClauses = List.Select(
            {SelectClause, ExpandClause, FilterClause, OrderByClause, TopClause, SkipClause, CountClause},
            each _ <> null
        ),

        QueryString = if List.Count(AllClauses) > 0
            then "?" & Text.Combine(AllClauses, "&")
            else "",

        EntitySetUrl = baseUrl & "/" & entitySet & QueryString
    in
        EntitySetUrl,

    // Handles nested $expand with per-navigation-property options
    BuildExpandClause = (expandSpec as list) as text =>
    let
        ExpandParts = List.Transform(expandSpec, each
            if _ is text then _
            else if _ is record then
                let
                    NavProp = _[Property],
                    NestedOpts = Record.FieldOrDefault(_, "Options", []),
                    NestedSelect = if Record.HasFields(NestedOpts, "$select")
                        then "$select=" & Text.Combine(NestedOpts[#"$select"], ",")
                        else null,
                    NestedFilter = if Record.HasFields(NestedOpts, "$filter")
                        then "$filter=" & NestedOpts[#"$filter"]
                        else null,
                    NestedTop = if Record.HasFields(NestedOpts, "$top")
                        then "$top=" & Number.ToText(NestedOpts[#"$top"])
                        else null,
                    NestedClauses = List.Select({NestedSelect, NestedFilter, NestedTop}, each _ <> null),
                    NestedQuery = if List.Count(NestedClauses) > 0
                        then "(" & Text.Combine(NestedClauses, ";") & ")"
                        else ""
                in
                    NavProp & NestedQuery
            else error "Invalid expand specification"
        )
    in
        Text.Combine(ExpandParts, ",")
in
    BuildODataUrl

Notice the nested expand syntax: OData v4 allows $expand=salesOrderLines($select=ItemNo,Quantity,UnitPrice;$filter=Quantity gt 0) — those nested options use semicolons as separators inside the parentheses, not ampersands. This trips up a lot of hand-rolled implementations.

Let's see what this looks like in practice for our Business Central scenario:

let
    BaseUrl = "https://api.businesscentral.dynamics.com/v2.0/MyTenant/Production/api/v2.0",

    // Build a URL to fetch sales orders with expanded lines and customer info
    SalesOrderUrl = BuildODataUrl(
        BaseUrl,
        "salesOrders",
        [
            #"$select" = {"id", "number", "orderDate", "customerNumber", "totalAmountIncludingTax", "status"},
            #"$expand" = {
                [
                    Property = "salesOrderLines",
                    Options = [
                        #"$select" = {"lineType", "description", "itemSystemId", "quantity", "unitPrice", "lineAmount"},
                        #"$filter" = "lineType eq 'Item'",
                        #"$top" = 50
                    ]
                ]
            },
            #"$filter" = "orderDate ge 2024-01-01 and status eq 'Open'",
            #"$orderby" = {"orderDate desc"},
            #"$top" = 100
        ]
    )
in
    SalesOrderUrl
// Output: https://api.businesscentral.dynamics.com/.../salesOrders
//         ?$select=id,number,orderDate,customerNumber,totalAmountIncludingTax,status
//         &$expand=salesOrderLines($select=lineType,description,itemSystemId,quantity,unitPrice,lineAmount;$filter=lineType eq 'Item';$top=50)
//         &$filter=orderDate%20ge%202024-01-01%20and%20status%20eq%20'Open'
//         &$orderby=orderDate desc
//         &$top=100

---

Step 3: Building a Filter Expression Library

Rather than building filter strings by hand — which is brittle and injection-prone — let's create a small library of filter expression builders. This is where the real power of Writing Custom M Functions from Scratch in Power Query pays off.

let
    ODataFilter = [
        // Comparison operators
        Eq = (field as text, value as any) as text =>
            field & " eq " & FormatValue(value),

        Ne = (field as text, value as any) as text =>
            field & " ne " & FormatValue(value),

        Gt = (field as text, value as any) as text =>
            field & " gt " & FormatValue(value),

        Ge = (field as text, value as any) as text =>
            field & " ge " & FormatValue(value),

        Lt = (field as text, value as any) as text =>
            field & " lt " & FormatValue(value),

        Le = (field as text, value as any) as text =>
            field & " le " & FormatValue(value),

        // Logical combinators
        And = (expressions as list) as text =>
            "(" & Text.Combine(expressions, " and ") & ")",

        Or = (expressions as list) as text =>
            "(" & Text.Combine(expressions, " or ") & ")",

        Not = (expression as text) as text =>
            "not (" & expression & ")",

        // String functions
        Contains = (field as text, value as text) as text =>
            "contains(" & field & "," & "'" & EscapeODataString(value) & "')",

        StartsWith = (field as text, value as text) as text =>
            "startswith(" & field & "," & "'" & EscapeODataString(value) & "')",

        // Collection operators
        In = (field as text, values as list) as text =>
            field & " in (" & Text.Combine(
                List.Transform(values, each FormatValue(_)),
                ","
            ) & ")",

        // Null checks
        IsNull = (field as text) as text => field & " eq null",
        IsNotNull = (field as text) as text => field & " ne null"
    ],

    // Format an M value to its OData literal representation
    FormatValue = (value as any) as text =>
        if value is text then "'" & EscapeODataString(value) & "'"
        else if value is number then Number.ToText(value)
        else if value is logical then if value then "true" else "false"
        else if value is date then
            "cast(" & Date.ToText(value, "yyyy-MM-dd") & ",Edm.Date)"
        else if value is datetime then
            "cast(" & DateTime.ToText(value, "yyyy-MM-ddTHH:mm:ssZ") & ",Edm.DateTimeOffset)"
        else if value is datetimezone then
            DateTimeZone.ToText(value, "yyyy-MM-ddTHH:mm:sszzz")
        else if value = null then "null"
        else error "Unsupported value type for OData filter: " & Value.Type(value),

    // Escape single quotes in OData string literals
    EscapeODataString = (value as text) as text =>
        Text.Replace(value, "'", "''")
in
    ODataFilter

Now building a filter expression reads almost like a domain-specific language:

let
    StartDate = #date(2024, 1, 1),
    EndDate = #date(2024, 12, 31),
    StatusValues = {"Open", "Released"},

    // Build: (orderDate ge 2024-01-01 and orderDate le 2024-12-31) and status in ('Open','Released')
    DateRangeFilter = ODataFilter.And({
        ODataFilter.Ge("orderDate", StartDate),
        ODataFilter.Le("orderDate", EndDate)
    }),

    StatusFilter = ODataFilter.In("status", StatusValues),

    CombinedFilter = ODataFilter.And({DateRangeFilter, StatusFilter})
in
    CombinedFilter
// Result: "((orderDate ge cast(2024-01-01,Edm.Date) and orderDate le cast(2024-12-31,Edm.Date)) and status in ('Open','Released'))"

This approach also makes it safe to wire up Power Query parameters directly into filter expressions without worrying about injection — since you're formatting typed M values through FormatValue, a text parameter containing a single quote gets properly escaped to ''.

---

Step 4: Implementing Server-Side Pagination

OData services return a maximum page size — often 100 or 1000 records — and include a @odata.nextLink property in the response JSON when more records are available. Your client needs to follow these continuations until there's no next link, then concatenate all the pages.

This is a recursive accumulation pattern. For a deep dive on the iterator pattern, see Streaming and Pagination Patterns in M: Handling Large APIs and Multi-Page Data Sources with Custom Iterators. Here's our implementation:

let
    FetchODataPages = (
        initialUrl as text,
        requestHeaders as record,
        optional maxPages as number
    ) as list =>
    let
        MaxPageCount = maxPages ?? 500,  // Safety ceiling

        FetchPage = (url as text) as record =>
        let
            RawResponse = Web.Contents(
                url,
                [
                    Headers = requestHeaders,
                    ManualStatusHandling = {400, 401, 403, 404, 429, 500, 503}
                ]
            ),
            StatusCode = Value.Metadata(RawResponse)[Response.Status],

            // Handle rate limiting with a descriptive error
            _ = if StatusCode = 429
                then error Error.Record(
                    "OData.RateLimitExceeded",
                    "Server returned 429 Too Many Requests. Reduce refresh frequency.",
                    [Url = url]
                )
                else if StatusCode >= 400
                then error Error.Record(
                    "OData.RequestFailed",
                    "HTTP " & Number.ToText(StatusCode) & " from OData endpoint",
                    [Url = url, StatusCode = StatusCode]
                )
                else null,

            JsonResponse = Json.Document(RawResponse),
            DataRows = Record.FieldOrDefault(JsonResponse, "value", {}),
            NextLink = Record.FieldOrDefault(JsonResponse, "@odata.nextLink", null),
            Count = Record.FieldOrDefault(JsonResponse, "@odata.count", null)
        in
            [
                Rows = DataRows,
                NextLink = NextLink,
                Count = Count
            ],

        // Accumulate pages using List.Generate
        PageResults = List.Generate(
            () => [Page = FetchPage(initialUrl), PageNumber = 1, Url = initialUrl],
            each _[Page][NextLink] <> null and _[PageNumber] <= MaxPageCount,
            each [
                Page = FetchPage(_[Page][NextLink]),
                PageNumber = _[PageNumber] + 1,
                Url = _[Page][NextLink]
            ],
            each _[Page][Rows]
        ),

        // We also need the first page's rows (List.Generate's selector is for subsequent states)
        FirstPage = FetchPage(initialUrl),
        AllRows = if FirstPage[NextLink] <> null
            then {FirstPage[Rows]} & PageResults
            else {FirstPage[Rows]}
    in
        List.Combine(AllRows)
in
    FetchODataPages

Now we can build the full data fetching function that combines URL construction, request execution, and pagination:

let
    FetchODataEntitySet = (
        baseUrl as text,
        entitySet as text,
        requestHeaders as record,
        optional queryOptions as record
    ) as table =>
    let
        RequestUrl = BuildODataUrl(baseUrl, entitySet, queryOptions),
        AllRows = FetchODataPages(RequestUrl, requestHeaders, null),

        // Convert the list of JSON records to a table
        // Each row is an M record from the JSON response
        ResultTable = if List.Count(AllRows) = 0
            then Table.FromRows({}, {})
            else Table.FromList(
                AllRows,
                Record.FieldNames,
                null,
                null,
                ExtraValues.Ignore
            )
    in
        ResultTable
in
    FetchODataEntitySet

Actually, Table.FromList with Record.FieldNames splitter won't work cleanly for JSON records. Let's do this properly:

let
    JsonRecordsToTable = (rows as list) as table =>
    let
        // Get the union of all field names across all records
        // (handles sparse responses where some records have fewer fields)
        AllFieldNames = List.Distinct(
            List.Combine(
                List.Transform(rows, each Record.FieldNames(_))
            )
        ),
        // Convert each record to a row in field-name order
        TableData = List.Transform(
            rows,
            each let r = _ in List.Transform(AllFieldNames, each Record.FieldOrDefault(r, _, null))
        )
    in
        Table.FromRows(TableData, AllFieldNames)
in
    JsonRecordsToTable

---

Step 5: Handling Entity Expansion and Nested Table Conversion

When you use $expand, the OData service returns nested JSON objects or arrays inside each row. After JsonRecordsToTable, these columns contain M records or lists — not expanded tables. You need to decide how to handle them based on your data model.

For a one-to-many expansion like salesOrderLines, you have three strategies:

Strategy A: Expand in place — flattens the table so each line item becomes a row, duplicating order header fields.

Strategy B: Keep as nested table — store the expanded data as a nested Table column for later use with Table.ExpandTableColumn.

Strategy C: Separate queries — query orders and lines independently, then join in Power Query.

Strategy B is usually the right choice for enterprise reporting, because it preserves the hierarchical relationship without data duplication, and Power Query's Table.ExpandTableColumn is well-optimized:

let
    ProcessExpandedColumn = (
        sourceTable as table,
        columnName as text,
        isCollection as logical
    ) as table =>
    let
        Processed = if isCollection then
            // List of records → nested table
            Table.TransformColumns(
                sourceTable,
                {
                    {
                        columnName,
                        each if _ = null then #table({}, {})
                             else if _ is list then JsonRecordsToTable(_)
                             else #table({}, {}),
                        type table
                    }
                }
            )
        else
            // Single record → expand fields with prefix
            Table.ExpandRecordColumn(
                sourceTable,
                columnName,
                Record.FieldNames(
                    List.First(
                        List.Select(
                            Table.Column(sourceTable, columnName),
                            each _ <> null
                        ),
                        []
                    )
                ),
                List.Transform(
                    Record.FieldNames(
                        List.First(
                            List.Select(
                                Table.Column(sourceTable, columnName),
                                each _ <> null
                            ),
                            []
                        )
                    ),
                    each columnName & "." & _
                )
            )
    in
        Processed
in
    ProcessExpandedColumn

---

Step 6: Implementing Real Filter Pushdown Logic

Here's where this lesson pays its biggest dividend. "Filter pushdown" in the context of a custom OData client means ensuring that your filter predicates are part of the URL you send to the server — not applied as M transformations after the data lands in Power Query.

The distinction is critical for performance. If you write:

// ❌ This fetches ALL sales orders, then filters in Power Query memory
let
    AllOrders = FetchODataEntitySet(BaseUrl, "salesOrders", Headers, null),
    Filtered = Table.SelectRows(AllOrders, each [orderDate] >= #date(2024, 1, 1))
in
    Filtered

versus:

// ✅ This sends the filter to the server as part of the OData URL
let
    FilteredOrders = FetchODataEntitySet(
        BaseUrl,
        "salesOrders",
        Headers,
        [
            #"$filter" = ODataFilter.And({
                ODataFilter.Ge("orderDate", #date(2024, 1, 1)),
                ODataFilter.Le("orderDate", #date(2024, 12, 31))
            })
        ]
    )
in
    FilteredOrders

The second version sends ?$filter=orderDate ge 2024-01-01 and orderDate le 2024-12-31 in the URL. The server evaluates this against its database index and returns only matching rows. For a table with 4 million orders, the difference is fetching 400 rows versus 4 million.

For scenarios where you want to support incremental refresh through Power Query's RangeStart and RangeEnd parameters — which is the pattern for scheduled refreshes in Power BI — you can wire these directly into the filter builder. This connects naturally to Implementing Incremental Refresh Logic in Power Query M: RangeStart, RangeEnd, and Folding-Compatible Filter Patterns:

let
    // These would be defined as Power Query parameters in production
    RangeStart = #datetimezone(2024, 1, 1, 0, 0, 0, 0, 0),
    RangeEnd = #datetimezone(2024, 4, 1, 0, 0, 0, 0, 0),

    // Convert to dates for OData filter (BC uses Edm.Date for orderDate)
    StartDate = DateTime.Date(DateTimeZone.RemoveZone(RangeStart)),
    EndDate = DateTime.Date(DateTimeZone.RemoveZone(RangeEnd)),

    IncrementalFilter = ODataFilter.And({
        ODataFilter.Ge("orderDate", StartDate),
        ODataFilter.Lt("orderDate", EndDate)  // Note: Lt, not Le, to avoid boundary overlap
    }),

    SalesOrders = FetchODataEntitySet(
        BaseUrl,
        "salesOrders",
        Headers,
        [
            #"$select" = {"id", "number", "orderDate", "customerNumber", "totalAmountIncludingTax"},
            #"$filter" = IncrementalFilter,
            #"$orderby" = {"orderDate asc"}
        ]
    )
in
    SalesOrders

---

Step 7: Caching Metadata to Eliminate Repeated Fetches

One of the worst performance characteristics of the built-in OData.Feed connector is that it fetches $metadata on every query evaluation. In a complex report with 15 queries all hitting the same OData service, that's 15 metadata requests before any actual data flows.

Since M is a lazy functional language with no global mutable state, caching requires some creativity. The reliable approach is to create a dedicated ODataMetadata query in your Power Query model that fetches and stores the parsed metadata as a record, then reference that query from your data-fetching queries.

// Query: ODataMetadata (set to "Enable load: Off" in the model)
let
    BaseUrl = "https://api.businesscentral.dynamics.com/v2.0/MyTenant/Production/api/v2.0",
    Metadata = FetchServiceMetadata(BaseUrl, BcAuthHeaders)
in
    Metadata

// Query: SalesOrders — references ODataMetadata instead of re-fetching
let
    // Use pre-fetched metadata to understand the entity structure
    SalesOrderEntityType = ODataMetadata[EntityTypes][salesOrder],
    NavProps = SalesOrderEntityType[NavigationProperties],
    HasSalesOrderLines = Record.HasFields(NavProps, "salesOrderLines"),

    // Only expand if the navigation property exists in metadata
    ExpandSpec = if HasSalesOrderLines
        then {[Property = "salesOrderLines", Options = [#"$select" = {"lineType", "quantity", "unitPrice"}]]}
        else {},

    SalesOrders = FetchODataEntitySet(
        ODataMetadata[BaseUrl],
        "salesOrders",
        BcAuthHeaders,
        [
            #"$filter" = ODataFilter.Ge("orderDate", StartDate),
            #"$expand" = ExpandSpec
        ]
    )
in
    SalesOrders

---

Step 8: Handling Enterprise API Quirks

Real enterprise OData implementations diverge from the spec in consistent, predictable ways. Here are the patterns you'll encounter and how to handle them in your custom client.

Quirk 1: The service returns 200 with an OData error body

Some middleware layers intercept HTTP errors and return a 200 OK with a JSON body containing an error property. Add a check after parsing the JSON:

let
    CheckODataError = (jsonResponse as record) as record =>
    let
        HasError = Record.HasFields(jsonResponse, "error"),
        ErrorDetail = if HasError then jsonResponse[error] else null,
        _ = if HasError then
            error Error.Record(
                "OData.ServiceError",
                Record.FieldOrDefault(ErrorDetail, "message", "Unknown OData error"),
                [
                    Code = Record.FieldOrDefault(ErrorDetail, "code", null),
                    InnerError = Record.FieldOrDefault(ErrorDetail, "innererror", null)
                ]
            )
            else null
    in
        jsonResponse
in
    CheckODataError

Quirk 2: nextLink uses a different base URL than the original request

This happens in load-balanced environments where the service document is at one URL but continuations are served from a different node. Never assume nextLink shares the same base URL — use the full URL from nextLink as-is:

// ✅ Always use nextLink verbatim
NextPage = FetchPage(_[Page][NextLink])

// ❌ Don't reconstruct the URL
NextPage = FetchPage(BaseUrl & "/" & EntitySet & "?$skiptoken=" & ExtractToken(_[Page][NextLink]))

Quirk 3: The service doesn't support $count

Some servers return a 400 error if you include $count=true. Build this as an optional feature in your client and test it before enabling:

let
    SupportsCount = (baseUrl as text, entitySet as text, headers as record) as logical =>
    let
        TestUrl = baseUrl & "/" & entitySet & "?$top=1&$count=true",
        Response = Web.Contents(TestUrl, [Headers = headers, ManualStatusHandling = {400, 501}]),
        StatusCode = Value.Metadata(Response)[Response.Status]
    in
        StatusCode = 200
in
    SupportsCount

For robust error handling in your client overall, the patterns from Error Handling and Try-Otherwise Patterns in M: Building Production-Ready Power Query Solutions are essential — particularly wrapping individual page fetches in try...otherwise to distinguish transient HTTP failures from structural problems.

---

Hands-On Exercise

Build a complete OData client for a publicly accessible OData v4 service. The OData.org sample service at https://services.odata.org/V4/TripPinServiceRW/ is ideal for this exercise since it's free, requires no authentication, and has realistic navigation properties.

Exercise Tasks:

1. Fetch and parse the metadata from https://services.odata.org/V4/TripPinServiceRW/$metadata. Extract all entity types and identify which ones have navigation properties. Print a structured record showing entity type names and their navigation property names.

2. Build a URL for the People entity set that:

   • Selects UserName, FirstName, LastName, Emails
   • Expands the Friends navigation property, selecting only UserName and FirstName for each friend
   • Filters to people whose FirstName starts with "S"
   • Orders by LastName asc

3. Implement pagination against the People entity set with $top=5. Verify that your accumulator correctly collects all pages and that the total row count matches $count=true (if supported).

4. Add error handling so that if a page fetch fails with HTTP 503, your code retries once after a brief delay, and if the retry also fails, surfaces a descriptive error record rather than crashing Power Query.

5. Cache the metadata query in a separate M query and reference it from your People and Airlines data queries. Verify (using Query Diagnostics) that the metadata endpoint is only called once per refresh, not once per data query.

---

Common Mistakes & Troubleshooting

Mistake 1: Double-encoding filter values

If you call Uri.EscapeDataString on the entire query string rather than just the filter clause value, you'll encode the $ signs and = signs in the option names, producing %24filter%3D instead of $filter=. Encode only the filter expression value, not the option name.

Mistake 2: Assuming $expand eliminates the need for $select on the parent entity

When you use $expand, the server returns the expanded navigation property in addition to all parent entity fields. If you have a wide entity (Business Central's salesOrder has 80+ fields), you'll still fetch all of them unless you explicitly $select on the parent. Always use $select alongside $expand.

Mistake 3: Not handling the sparse record problem in pagination

If your OData service returns different fields on different pages (this happens when the server applies different views to different data ranges), JsonRecordsToTable will produce tables with different column sets per page. List.Combine before converting to a table is the fix — ensure you collect all rows as records first, then do a single JsonRecordsToTable call over the entire combined list with the union of all field names.

Mistake 4: Using Table.SelectRows after FetchODataEntitySet and expecting pushdown

Your custom FetchODataEntitySet function returns a regular M table. M has no mechanism to inspect what a function does and retroactively push filters into it — query folding only works for data sources that implement the Table.View pattern with explicit handler functions. If you want true pushdown, your filter predicates must be inside the queryOptions record passed to FetchODataEntitySet, not applied as Table.SelectRows afterward.

Mistake 5: Treating all OData dates as datetime

OData v4 distinguishes between Edm.Date (date only), Edm.DateTimeOffset (date with timezone), and Edm.DateTime (deprecated in v4 but still in many enterprise services). If you format a comparison as orderDate eq 2024-01-15T00:00:00Z against an Edm.Date field, most servers will reject the filter or return zero rows. Always check the property type in your parsed metadata before constructing filter literals.

Troubleshooting: Filters not being applied server-side

Use Power BI's Query Diagnostics feature (or inspect the actual HTTP requests with Fiddler/Charles) to verify that your $filter clause is present in the outbound URL. If the URL is correct but row counts suggest the filter isn't being applied, the server may be accepting the filter syntactically but evaluating it in middleware. Check the OData service's documentation for supported filterable properties — many implementations only push down filters on indexed columns.

---

Summary & Next Steps

You've built a complete OData feed client from scratch: one that parses service metadata, constructs system query options programmatically, handles server-side pagination through @odata.nextLink, manages entity expansion with proper collection vs. single-entity handling, and implements genuine filter pushdown rather than relying on post-fetch M transformations. You've also covered the enterprise quirks — error body detection, nextLink URL handling, and sparse record normalization — that separate production-grade clients from fragile demos.

The architecture we've built is also composable. The BuildODataUrl, ODataFilter, and FetchODataPages functions are independent, testable units that you can store in a reusable function library in Power Query and reference across multiple reports and datasets.

Where to go from here:

• Authentication: If your OData endpoint uses OAuth 2.0 (as Business Central does in production), you'll need to implement token acquisition as a prerequisite to every request. Implementing Custom Data Connector Authentication and OAuth Flows in Power Query M Language covers this end-to-end.

• Package as a .mez connector: Once your client is solid, consider packaging it as a custom connector so it can be deployed across your organization with centralized credential management. Implementing Custom Connectors with M Language: Building, Packaging, and Deploying .mez Extensions for Enterprise Data Sources walks through the full packaging process.

• Schema validation: Add type enforcement over the raw JSON output so that schema changes in the OData service surface as clear data quality errors rather than silent type mismatches downstream in your model.

• Performance profiling: Use M's diagnostic patterns to measure the wall-clock time each page fetch adds and tune your $top page size accordingly — larger pages mean fewer round trips but higher per-request latency on the server, and the optimal value varies by service.

The OData protocol is more powerful than most Power Query practitioners realize, and a well-built custom client is often the difference between a report that refreshes in 90 seconds and one that times out after 20 minutes.