OData.Feed

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function OData.Feed

One of the most powerful features of Power Query is the M language, which is used to write custom functions and transforms. In this article, we’ll take a closer look at the M code behind the OData.Feed function in Power Query, which is used to connect to OData data sources.

What is OData?

OData is an open standard protocol for querying and updating data, based on HTTP and RESTful web services. It is designed to enable interoperability between different systems and platforms, by providing a common language for data exchange.

OData data sources can be accessed using a URL, which returns a response in XML or JSON format. This response can then be parsed and transformed using Power Query, using the OData.Feed function.

How the OData.Feed Function Works

The OData.Feed function is used to connect to an OData data source and retrieve data. It takes a single argument, which is a URL string that points to the data source.

Here’s an example of the OData.Feed function in action:

let

url = “https://services.odata.org/V4/Northwind/Northwind.svc”,

source = OData.Feed(url)

in

source

In this example, we're connecting to the Northwind sample data source, which is hosted by the OData.org website. The OData.Feed function retrieves the data from the URL and returns a table that can be further transformed using Power Query.

Understanding the M Code Behind the OData.Feed Function

Behind the scenes, the OData.Feed function is using M code to perform the following steps:

1. Send an HTTP GET request to the specified URL

2. Parse the response XML or JSON into a table format

3. Apply any specified transformations to the table

Here's an example of the M code that the OData.Feed function generates:

let

url = “https://services.odata.org/V4/Northwind/Northwind.svc”,

source = Xml.Tables(Web.Contents(url)),

table = source{[Name=”Product”]}[Data]

in

table

In this example, the OData.Feed function is sending an HTTP GET request to the Northwind data source, using the Web.Contents function. This function retrieves the raw XML response from the web service.

The Xml.Tables function is then used to parse the response XML into a table format. This function generates a list of tables, one for each entity set in the OData data source.

In our example, we're interested in the "Product" entity set, so we use the { } syntax to select the table that corresponds to this entity set.

Finally, we apply any specified transformations to the table, using the standard Power Query transformation functions.

Customizing the OData.Feed Function

The OData.Feed function can be customized using a variety of optional parameters. These parameters can be used to filter data, select specific columns, and control how the data is retrieved.

Here are some examples of the optional parameters that can be used with the OData.Feed function:

- Filter: Used to specify a filter query to be applied to the data source

- Select: Used to specify which columns to include in the returned data

- Expand: Used to include related entities in the returned data

- Skip: Used to skip a specified number of rows in the data source

- Top: Used to limit the number of rows returned by the data source

Here's an example of using the OData.Feed function with some of these optional parameters:

let

url = “https://services.odata.org/V4/Northwind/Northwind.svc”,

source = OData.Feed(

url,

[Filter=”CategoryID eq 1″, Select=”ProductID,ProductName,UnitPrice”, Skip=10, Top=5]

)

in

source

In this example, we're using the OData.Feed function with the following options:

- Filter: We're filtering the data to only include products with a CategoryID of 1

- Select: We're only including the ProductID, ProductName, and UnitPrice columns in the returned data

- Skip: We're skipping the first 10 rows in the data source

- Top: We're limiting the returned data to only include the next 5 rows after the skipped rows

The OData.Feed function in Power Query is a powerful tool for connecting to OData data sources. By understanding the M code behind this function, you can customize it to suit your specific needs and gain even more control over your data transformations.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)