Downloads
Power Query MList: pick and buildIntermediate

List.Difference

Returns the items in the first list that are not in the second list.

Syntax
List.Difference(list1, list2, optional equationCriteria)

Returns: A list of the items found only in the first list.

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

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.

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics