List.Difference Function in Power Query M (5 Easy Examples)

If you have two lists and want to find the items in the first one that are not in the second, the List.Difference function is what you reach for. It returns those leftover items as a new list.

Table of Contents

Syntax of List.Difference Function

List.Difference(list1 as list, list2 as list, optional equationCriteria as any) as list

where

list1 (required, list). The source list. The items that come back are taken from here.
list2 (required, list). The list to subtract. Any item found here is removed from list1.
equationCriteria (optional, any). Controls how two items are compared for equality, for example a Comparer function for case-insensitive matching. Omit it for the default case-sensitive match.

Returns: a list containing the items from list1 that do not appear in list2. If every item in list1 is found in list2, it returns an empty list.

In plain terms, you hand it two lists and it gives you back whatever is in the first list but missing from the second.

Example 1: Subtract one list of numbers from another

Start with a list of numbers and remove the values that appear in a second list.

let
    Source = List.Difference({10,20,30,40,50},{20,40})
in
    Source

Result: {10, 30, 50}

20 and 40 are in the second list, so they get dropped. The leftover items keep their original order, so if you want them sorted, run the result through List.Sort.

Example 2: Keep duplicates that are not subtracted

List.Difference does not remove duplicates on its own. It only drops items that match the second list.

let
    Source = List.Difference({"red","red","blue","green","red"},{"green"})
in
    Source

Result: {"red", "red", "blue", "red"}

Only green is subtracted. All three red entries survive, and the order from the first list is preserved.

Example 3: Ignore case with a comparer

By default the comparison is case-sensitive, so "apple" would not match "Apple". Pass Comparer.OrdinalIgnoreCase as the third argument to match regardless of case.

let
    Source = List.Difference({"Apple","Mango","Cherry"},{"apple","MANGO"},Comparer.OrdinalIgnoreCase)
in
    Source

Result: {"Cherry"}

With the comparer, "apple" knocks out "Apple" and "MANGO" knocks out "Mango". Only "Cherry" has no match in the second list.

Example 4: Find discontinued products across two tables

Say you have last month’s product SKUs and this month’s product SKUs in two separate tables, and you want the SKUs that were dropped this month.

Here is last month’s data in a table named LastMonth:

SKU
SKU-101
SKU-102
SKU-103
SKU-104
SKU-105

And this month’s data in a table named ThisMonth:

SKU
SKU-102
SKU-104
SKU-105

Pull each SKU column into a list and subtract this month from last month:

let
    Prev = Excel.CurrentWorkbook(){[Name="LastMonth"]}[Content][SKU],
    Curr = Excel.CurrentWorkbook(){[Name="ThisMonth"]}[Content][SKU],
    Discontinued = List.Difference(Prev,Curr)
in
    Discontinued

This returns the SKUs that were present last month but are gone this month.

The result keeps only the discontinued SKUs:

SKU
SKU-101
SKU-103

SKU-101 and SKU-103 are the only codes from LastMonth that do not appear in ThisMonth. If your data lives in full tables rather than single columns, a Table.Join with JoinKind.LeftAnti does the same job at the row level.

Example 5: Get an empty list when everything matches

When every item in the first list is found in the second list, there is nothing left to return.

let
    Source = List.Difference({"x","y"},{"x","y","z"})
in
    Source

Result: {} (an empty list)

Both "x" and "y" are in the second list, so the result is empty. The extra "z" in the second list is ignored, since the function only looks at what to remove from the first.

Things to keep in mind with List.Difference

It is one-directional, not a symmetric difference. You only get items from list1 that are missing from list2. Items unique to list2 are never reported. Swap the arguments to look the other way.
A matched value is removed everywhere it appears. If a value is in list2, every occurrence of it in list1 is dropped, not just the first one. To simply test whether one value sits inside a list, use List.Contains instead.
Comparison is case-sensitive by default. "NYC" and "nyc" count as different items. Pass Comparer.OrdinalIgnoreCase as equationCriteria to treat them as equal.
Both lists should hold the same kind of value. Comparing items that Power Query cannot line up against each other can throw an error, so keep numbers with numbers and text with text before you subtract.

Common questions about List.Difference

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

Both subtract one list from another and remove every matching occurrence, so for plain values they return the same result. The real difference is that List.Difference takes an optional equationCriteria argument for case-insensitive or custom matching, while List.RemoveItems has no such option.

Does List.Difference remove duplicates from my list?

No. It only drops values that appear in the second list. To remove repeated values within a single list, use List.Distinct instead.

List of All Power Query Functions

Related Power Query Functions / Articles: