ItemExpression.Item

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function ItemExpression.Item

Understanding the ItemExpression.Item Function

The ItemExpression.Item function in Power Query is used to extract a value from a list or a record based on the index or the name of the item. This function takes two parameters: the first parameter is the list or record, and the second parameter is the index or the name of the item.

Here is the syntax of the ItemExpression.Item function:

ItemExpression.Item(listOrRecord as list or record, indexOrName as any) as any

The function returns the value at the specified index or name. If the index or name is not found, the function returns a null value.

Using the ItemExpression.Item Function

To understand the M code behind the ItemExpression.Item function, let's consider an example. Suppose we have a list of products with their corresponding prices, and we want to extract the price of a specific product based on its name. Here is the list:

| Product | Price |

|---------|-------|

| Apple | 1.5 |

| Banana | 2 |

| Orange | 3 |

| Mango | 4 |

To extract the price of a specific product, we can use the ItemExpression.Item function as follows:

let

products = #table({“Product”, “Price”}, {{“Apple”, 1.5}, {“Banana”, 2}, {“Orange”, 3}, {“Mango”, 4}}),

productName = “Apple”,

price = products{[Product=productName]}[Price]

in

price

In the above code, we first define a table called "products" that contains the list of products and their prices. We then define a variable called "productName" that contains the name of the product we want to extract the price for. Finally, we use the ItemExpression.Item function to extract the price of the product based on its name.

The expression "products{[Product=productName]}[Price]" uses the curly brackets to access the records in the "products" table. The square brackets are used to filter the records based on the "Product" column that matches the value of the "productName" variable. Finally, we use the square brackets again to extract the value of the "Price" column.

The M Code Behind the ItemExpression.Item Function

Now that we have a basic understanding of how to use the ItemExpression.Item function, let's take a look at the M code behind this function.

let

Source = (listOrRecord as list or record, indexOrName as any) as any =>

if Type.Is(listOrRecord, type list) then

List.PositionOf(listOrRecord, indexOrName)

else if Type.Is(listOrRecord, type record) then

Record.Field(listOrRecord, indexOrName)

else

error “Value is not a list or record”,

Function = Value.ReplaceType(Source, type function(list or record, any) as any)

in

Function

The above code defines a function called "Source" that takes two parameters, "listOrRecord" and "indexOrName", and returns the value at the specified index or name.

The function first checks if the "listOrRecord" parameter is a list or a record using the Type.Is function. If the parameter is a list, the function uses the List.PositionOf function to return the value at the specified index. If the parameter is a record, the function uses the Record.Field function to return the value with the specified name.

Finally, the code defines a variable called "Function" that uses the Value.ReplaceType function to replace the type of the "Source" function with a type that takes a list or record and any type as parameters and returns any type.

The ItemExpression.Item function in Power Query is a powerful tool that enables users to extract values from a list or a record based on the index or the name of the item. By understanding the M code behind this function, users can gain a deeper understanding of how Power Query works and how to use the M Language to perform complex 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)