OData.Feed

Updated on

OData.Feed is a Power Query M function that returns a table of OData feeds from an OData service specified by a URI and optional headers. The function returns the table with control over various options, such as query parameters, headers, timeouts, and OData version.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

OData.Feed(
   serviceUri as text,
   optional headers as nullable record,
   optional options as any,
) as any
ArgumentAttributeDescription
serviceUriA string that specifies the OData service by using a URI.
headersoptionalA record providing additional HTTP request headers.
optionsoptionalAn optional record that allows you to control a range of options. Details are provided below.

Description

The OData.Feed function retrieves data from an OData service specified by a URI, returning it as a table. It allows for customized data access through optional headers and various control options like query parameters and timeouts. By default, the function uses a series of settings like concurrent request handling, caching strategies, and OData protocol versions, but these can be tailored to specific needs.

You can specify an optional options record, which supports the following options:

  • Query: Add query parameters to the URL automatically, eliminating the need for manual escaping.
  • Headers: Provide additional HTTP request headers as a record.
  • ExcludedFromCacheKey: Exclude specified HTTP header keys from cache key calculations by listing them.
  • ApiKeyName : If the target site has a notion of an API key, this parameter can be used to specify the name (not the value) of the key parameter that must be used in the URL. The actual key value is provided in the credential.
  • Timeout : Specifying this value as a duration will change the timeout for an HTTP request. The default value is 600 seconds.
  • EnableBatch : A logical (true/false) that enables or disables the generation of an OData $batch request if the MaxUriLength is exceeded (default is false).
  • MaxUriLength : A number that indicates the max length of an allowed uri sent to an OData service. If exceeded and EnableBatch is true then the request will be made to an OData $batch endpoint, otherwise it will fail (default is 2048).
  • Concurrent : A logical (true/false) when set to true, requests to the service will be made concurrently. When set to false, requests will be made sequentially. When not specified, the value will be determined by the service’s AsynchronousRequestsSupported annotation. If the service does not specify whether AsynchronousRequestsSupported is supported, requests will be made sequentially.
  • ODataVersion : A number (3 or 4) that specifies the OData protocol version to use for this OData service. When not specified, all supported versions will be requested. The service version will be determined by the OData-Version header returned by the service.
  • FunctionOverloads : A logical (true/false) when set to true, function import overloads will be listed in the navigator as separate entries, when set to false, function import overloads will be listed as one union function in the navigator. Default value for V3: false. Default value for V4: true.
  • MoreColumns : A logical (true/false) when set to true, adds a “More Columns” column to each entity feed containing open types and polymorphic types. This will contain the fields not declared in the base type. When false, this field is not present. Defaults to false.
  • IncludeAnnotations : A comma separated list of namespace qualified term names or patterns to include with “*” as a wildcard. By default, none of the annotations are included.
  • IncludeMetadataAnnotations : A comma separated list of namespace qualified term names or patterns to include on metadata document requests, with “*” as a wildcard. By default, includes the same annotations as IncludeAnnotations.
  • OmitValues : Allows the OData service to avoid writing out certain values in responses. If acknowledged by the service, we will infer those values from the omitted fields. You can specify instructions in the options argument by using the OdataOmitValues.Type which currently includes
  • Implementation : Specifies the implementation of the OData connector to use. Valid values are “2.0” or null.

Examples

Connect to the TripPin OData service.

// Output: table
OData.Feed( "https://services.odata.org/V4/TripPinService" )

You can add a range of options to change the behaviour of the function. An example of using the OdataOmitValues.Nulls enumeration is:

OData.Feed(
    "https://analytics.dev.azure.com/yourstring",
    null,
    [ OmitValues = ODataOmitValues.Nulls, ODataVersion = 4 , Implementation = "2.0" ]
) 

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/odata-feed