List.RemoveItems

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.RemoveItems

In this article, we’ll dive deep into the M code behind the List.RemoveItems function, exploring its syntax, arguments, and practical applications.

Syntax and Arguments

The syntax for the List.RemoveItems function is as follows:

List.RemoveItems(list as list, values as list, optional equationCriteria as nullable function) as list

The function takes in three arguments:

- `list` (required): The list from which to remove items.

- `values` (required): The list of items to remove from the `list` argument.

- `equationCriteria` (optional): A function that determines how to compare the items in the `list` and `values` arguments.

Practical Applications

The List.RemoveItems function can be used to remove specific items from a list. For example, suppose you have a list of sales data that includes sales from different regions and countries. You might want to remove all the sales data from a specific region or country to focus on other data. In this case, you can use the List.RemoveItems function to remove the unwanted data from the list.

Here's an example of how to use the List.RemoveItems function to remove all sales data from the United States:

let

salesData = {{“United States”, 1000}, {“United Kingdom”, 1500}, {“Canada”, 800}},

removeCountries = {“United States”},

filteredSales = List.RemoveItems(salesData, removeCountries)

in

filteredSales

The output of this query will be:

{{“United Kingdom”, 1500}, {“Canada”, 800}}

As you can see, the sales data for the United States has been removed from the list.

Comparison Criteria

The List.RemoveItems function also allows you to specify a function that determines how to compare the items in the `list` and `values` arguments. This function is specified as the optional `equationCriteria` argument.

The default comparison criteria is a function that uses the EqualityComparer.Compare function, which compares the items for equality using the default comparison rules.

However, you can provide your own comparison function if the items in the `list` and `values` arguments need to be compared in a specific way. For example, suppose you have a list of products that includes product IDs and prices. You might want to remove all products that have a price of less than $10. In this case, you can use the List.RemoveItems function with a custom comparison function that compares the price of each product to $10.

Here's an example of how to use the List.RemoveItems function with a custom comparison function:

let

products = {{“Prod001”, 9.99}, {“Prod002”, 12.99}, {“Prod003”, 8.99}},

removeLowPrice = List.RemoveItems(products, {}, (x, y) => x{1} < 10),

filteredProducts = List.Select(products, each not List.Contains(removeLowPrice, _))

in

filteredProducts

The output of this query will be:

{{“Prod002”, 12.99}}

As you can see, the product with a price of less than $10 has been removed from the list.

Summary

In conclusion, the List.RemoveItems function is a powerful tool that can be used to remove specific items from a list in Power Query. Its syntax is simple and straightforward, and it allows you to specify a custom comparison function if needed. By mastering the List.RemoveItems function, you can take your Power Query skills to the next level and extract even more powerful insights from your data.

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)