List.RemoveItems Function in Power Query M (5 Examples)

List.RemoveItems takes two lists and returns the items from the first list that are not present in the second. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you have a list and want to strip out a set of specific values, like an email blocklist or a set of discontinued codes, this is the function you reach for.

Table of Contents

Syntax of List.RemoveItems Function

List.RemoveItems(list1 as list, list2 as list) as list

where

list1 (required, list). The list to remove items from.
list2 (required, list). The list of values to remove from list1.

Returns: a list containing the items of list1 that are not found in list2. If every item gets removed, you get an empty list {}.

In plain terms, you give it a list and a second list of values to drop, and it hands back what is left over.

Example 1: Remove a set of numbers from a list

Drop the values 2, 4, and 6 from a list of numbers.

let
    Source = List.RemoveItems({1,2,3,4,2,5,5},{2,4,6})
in
    Source

Result: {1, 3, 5, 5}

Both 2 values are gone, and so is the 4. The 6 is ignored since it was never in the list to begin with, and the two 5 values stay because they were not on the remove list.

Example 2: Filter out emails on a blocklist

A really common job is removing addresses that sit on a blocklist column.

Say you have a Blocklist table holding the addresses you never want to email.

Here is the starting data:

Email
ben@example.com
dan@example.com

You have a recipient list in your query, and you want to keep only the people who are not blocked. If you only needed to test whether a value sits in a list rather than remove it, List.Contains is the simpler choice.

let
    Blocked = Table.Column(Excel.CurrentWorkbook(){[Name="Blocklist"]}[Content],"Email"),
    Recipients = {"ana@example.com","ben@example.com","cara@example.com","dan@example.com","ben@example.com"},
    Source = List.RemoveItems(Recipients,Blocked)
in
    Source

Table.Column pulls the Email column into a list, then List.RemoveItems strips every recipient that appears on it.

Result: {"ana@example.com", "cara@example.com"}

Both copies of ben@example.com are removed, not just the first one, and dan@example.com is dropped too.

Example 3: Matching is case-sensitive

The function compares values exactly, so casing matters.

let
    Source = List.RemoveItems({"Apple","apple","APPLE","apple"},{"apple"})
in
    Source

Result: {"Apple", "APPLE"}

Only the lowercase apple entries are removed. Apple and APPLE survive because they do not match the removed value exactly.

Example 4: Matching is also type-exact

A number and the text version of that number are not the same value here.

let
    Source = List.RemoveItems({1,2,"2",3,2},{2})
in
    Source

Result: {1, "2", 3}

The numeric 2 values are removed, but the text "2" stays because text "2" does not equal the number 2.

Example 5: Remove discontinued product codes

Suppose you have a catalog of SKUs and a separate table of discontinued ones to drop.

Here is the starting data:

SKU
SKU-12
SKU-45

You want the live catalog with the discontinued codes pulled out.

let
    DropList = Table.Column(Excel.CurrentWorkbook(){[Name="Discontinued"]}[Content],"SKU"),
    Catalog = {"SKU-12","SKU-30","SKU-45","SKU-12","SKU-77"},
    Source = List.RemoveItems(Catalog,DropList)
in
    Source

Result: {"SKU-30", "SKU-77"}

Both copies of SKU-12 are removed along with SKU-45, leaving only the codes that are still active. To filter table rows by a removal list instead of working on a plain list, you can pair this idea with Table.SelectRows, and to strip out fully repeated rows there is Table.Distinct.

Things to keep in mind with List.RemoveItems

It removes every occurrence of a matched value, not just the first. One 2 in list2 strips all 2 values from list1 (Example 1). It is not a one-for-one cancellation.
Matching is case-sensitive. "apple" does not match "Apple". Normalize casing first with Text.Lower if you want a loose match.
Matching is type-exact too. The number 2 does not match the text "2". Convert with Text.From or Number.From so both sides are the same type.
Survivor order is preserved. Whatever is left comes back in its original order, so you do not need to re-sort.
Removing everything yields an empty list. If list2 covers every value, the result is {}, not an error.
It removes by value, not by position. To drop items by their index, use List.RemoveRange, List.RemoveFirstN, or List.RemoveLastN instead.
For “rows in one table with no match in another”, a join is cleaner. A JoinKind.LeftAnti Table.NestedJoin keeps only the unmatched rows, which is the table-level version of removing items.

Common questions about List.RemoveItems

What is the difference between List.RemoveItems and List.Difference?

Both return the items of list1 that are not in list2, and both remove every matched occurrence. The difference is that List.Difference accepts an optional equationCriteria argument for custom equality, while List.RemoveItems always uses default equality. Reach for List.RemoveItems for a plain blocklist, and List.Difference when you need a custom comparison.

List of All Power Query Functions

Related Power Query Functions / Articles: