Downloads
Power Query MList: pick and buildIntermediate

List.Intersect

Returns the values that appear in every one of the lists you give.

Syntax
List.Intersect(lists, optional equationCriteria)

Returns: A list of the common values, with duplicates removed.

What it does

List.Intersect finds the overlap. Give it several lists and it returns only the values that are in all of them. Use it to find vendors that appear in two payment files, or accounts that are used in both the budget and the ledger.

Syntax and arguments

Example

Two payment files list the vendors paid this month. The controller wants to see which vendors appear in both, because those may be duplicate payments.

let
    PaymentFileA = {"V-1001", "V-2044", "V-3300"},
    PaymentFileB = {"V-2044", "V-3300", "V-4500"},
    InBoth = List.Intersect({PaymentFileA, PaymentFileB})
in
    InBoth

InBoth is {"V-2044", "V-3300"}. Those two vendors need a look to confirm that each payment was intended.

Example: a second, different use

Accounts used in both the budget and the ledger: the intersection shows the accounts that can be compared line by line.

Things to watch

In business terms

Duplicate payment checks, matching customer lists between systems and finding accounts that exist in two places are typical intersections. Keep the list of matches as the exception report for review.

Related functions

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