List.Intersect
Returns the values that appear in every one of the lists you give.
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
lists: a list of lists. A value must appear in every inner list to be returned.equationCriteria(optional): how values are compared, such asComparer.OrdinalIgnoreCase.
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
- A value missing from any one list is dropped. The result can be empty if the lists have nothing in common.
- It returns values, not rows. To see the full rows for the matches, use a merge in a table.
- Capital letters matter by default. Pass Comparer.OrdinalIgnoreCase for names typed by people.
- A value repeated in both lists is returned once.
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.