ItemExpression.From

D

Back to top

T

Back to top

#

Back to top

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

What is the ItemExpression.From Function?

The ItemExpression.From function is a Power Query M function that is used to extract values from a list or record. It takes two arguments: the first argument is the list or record to extract values from, while the second argument specifies the item to extract.

Understanding the M Code

To better understand the M code behind the ItemExpression.From function, let us consider an example. Suppose we have a table with a column named ‘Fruits’, and each row in this column contains a record with two fields: ‘Name’ and ‘Quantity’. We can use the ItemExpression.From function to extract the quantity of each fruit as follows:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WCgZRCVJzslJQyKpWitWJVjNyclQykyzxKkKJFILyosB”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruits = _t]),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Fruits”, type any}}),

#”Expanded Fruits” = Table.ExpandRecordColumn(#”Changed Type”, “Fruits”, {“Name”, “Quantity”}, {“Fruits.Name”, “Fruits.Quantity”}),

#”Extracted Quantity” = Table.AddColumn(#”Expanded Fruits”, “Quantity”, each ItemExpression.From([Fruits.Quantity],”Quantity”))

in

#”Extracted Quantity”

In the above M code, we first create a source table from the data, and then transform the 'Fruits' column to contain records with two fields. We then expand the 'Fruits' column to create separate columns for the 'Name' and 'Quantity' fields. Finally, we use the ItemExpression.From function to extract the quantity of each fruit and create a new column named 'Quantity'.

The second argument in the ItemExpression.From function specifies the item to extract from the list or record. In the above example, we specify "Quantity" as the item to extract.

Best Practices for Using the ItemExpression.From Function

When using the ItemExpression.From function in Power Query, it is important to keep the following best practices in mind:

1. Specify the item to extract as a string.

2. Use the appropriate data type for the extracted item. For example, if extracting a number, ensure that the resulting column is of type number.

3. Use the function only when necessary. In some cases, it may be more efficient to use other functions such as Table.Column or Record.Field.

The ItemExpression.From function in Power Query is a powerful tool for extracting values from lists and records. By understanding the M code behind this function, you can use it more effectively in your queries and make your data transformations more efficient. Remember to keep best practices in mind when using this function to avoid errors and ensure optimal performance.

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)