<!-- https://learnsql.cpetoday.com/functions/m/list-difference -->
# List.Difference

Language: Power Query M
Category: List: pick and build
Syntax: List.Difference(list1, list2, optional equationCriteria)
Summary: Returns the items in the first list that are not in the second list.
Returns: A list of the items found only in the first list.
Level: Intermediate
Related: List.Intersect, List.Union, List.Numbers, List.Contains, List.Distinct

## What it does
List.Difference answers "what is in the first list but not in the second?" It is the standard tool for finding missing items: the invoice numbers that were never issued, the vendors that were not paid, or the accounts that are not in the mapping file. The order of the first list is kept.

## Syntax and arguments
- `list1`: the complete list you expect to see.
- `list2`: the list of items that actually exist.
- `equationCriteria` (optional): how values are compared, such as `Comparer.OrdinalIgnoreCase`.

## Example
Invoices are numbered in sequence from 1001 to 1010. The billing system export lists the invoice numbers it actually holds. The AR team needs the missing numbers, which may be voided or lost.

```powerquery
let
    Expected = List.Numbers(1001, 10),
    Issued = {1001, 1002, 1005, 1006, 1009, 1010},
    Missing = List.Difference(Expected, Issued)
in
    Missing
```

`Missing` is {1003, 1004, 1007, 1008}. Each one needs an answer: a void, a cancelled document or a gap that must be explained. With a real export, the second list comes from the invoice column of the table, such as `Invoices[invoiceNumber]`.

## Example: a second, different use
Vendors paid in the year that are not on the approved list: List.Difference of the paid vendors against the approved vendors shows the exceptions to review.

## Things to watch
- The order matters for the question. The result is what is in list1, so put the complete list first.
- Check the count of the result against what you expect, especially when the export might contain repeated numbers.
- Make sure both lists have the same type. Text "1003" and the number 1003 are different.
- For a larger gap check, consider a table join with a null test, because a merge keeps the rows you need for follow-up.

## In business terms
Missing invoice and check numbers, unmatched bank items, vendors not reconciled and accounts missing from a mapping are standard exception reports. Each missing item should have a documented reason before the close is signed off.
