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

The List.Intersect function finds the values that appear in every list you give it. Hand it a few lists and it returns only the items they all share.

Table of Contents

Syntax of List.Intersect Function

List.Intersect(lists as list, optional equationCriteria as any) as list

where:

lists (required, list): A list whose items are themselves lists. This is the part that trips people up. You pass one outer list that wraps all the lists you want to compare, like {ListA, ListB}, not two separate arguments.
equationCriteria (optional, any): Controls how values are compared. Pass Comparer.OrdinalIgnoreCase here to match values regardless of letter case.

Returns: a list of the elements common to all the lists, in the order they appear in the first list. If the lists share nothing, it returns an empty list.

In plain terms, you get back only the values that show up in every list you passed.

Example 1: Find Values Shared by Two Lists

Say you have two ingredient lists and you want the items both recipes use. Wrap both lists inside one outer list and pass that to List.Intersect.

let
    Source = List.Intersect({{"Salt","Pepper","Water","Flour"},{"Sugar","Water","Salt","Yeast"}})
in
    Source

Result: {"Salt", "Water"}

Only "Salt" and "Water" appear in both lists, so those are the only values returned. Notice the order matches the first list, where "Salt" comes before "Water".

Example 2: Find Values Shared by Three Lists

List.Intersect is not limited to two lists. Add as many as you like inside the outer list, and the result keeps only what every list has in common.

let
    Source = List.Intersect({{"Salt","Pepper","Water","Flour"},{"Sugar","Water","Salt","Yeast"},{"Salt","Oil","Water","Eggs"}})
in
    Source

Result: {"Salt", "Water"}

All three lists contain "Salt" and "Water". Items like "Pepper" or "Oil" show up in only one list, so they drop out.

Example 3: Match Values Without Caring About Case

By default, the comparison is case-sensitive, so "Salt" and "SALT" count as different values. To ignore case, pass Comparer.OrdinalIgnoreCase as the second argument.

let
    Source = List.Intersect({{"Salt","Pepper","Water","Flour"},{"SALT","sugar","WATER","yeast"}},Comparer.OrdinalIgnoreCase)
in
    Source

Result: {"Salt", "Water"}

Even though the second list spells the words in different cases, the match still works. The returned values keep the casing from the first list, so you get "Salt" and "Water".

Example 4: Confirm the Result Follows the First List’s Order

The order of the result always comes from the first list, not from any sorting. This example makes that easy to see using numbers.

let
    Source = List.Intersect({{5,4,3,2,1},{2,3,4}})
in
    Source

Result: {4, 3, 2}

The shared values are 2, 3, and 4. They come back as {4, 3, 2} because that is the order they sit in within the first list, even though the second list has them the other way around. If you actually want a sorted result, run the output through List.Sort.

Example 5: Keep Only Approved Items From a Table Column

A real task you hit often is checking a column of values against an allow-list. Here is a small OrderProducts table:

Product
Keyboard
Monitor
Webcam
Mouse
Charger

Pull the Product column into a list, then intersect it with your approved list. If you later want to count how many made the cut, pass the result to List.Count. The result is only the products that are both ordered and approved.

let
    Source = Excel.CurrentWorkbook(){[Name="OrderProducts"]}[Content],
    Products = Source[Product],
    Approved = List.Intersect({Products,{"Monitor","Mouse","Keyboard","Headset"}})
in
    Approved

Result: {"Keyboard", "Monitor", "Mouse"}

Webcam and Charger are not on the approved list, so they are left out. Headset is approved but was never ordered, so it does not appear either. The order follows the table column, the first list.

Things to keep in mind with List.Intersect

The first argument is a list of lists. Wrap every list you want to compare inside one outer pair of braces, like {ListA,ListB}. Passing them as separate arguments, like List.Intersect(ListA,ListB), throws an error because the function expects a single list argument.
The result order always follows the first list. The function never sorts the output.
The returned values keep the first list’s casing. If "Salt" is in the first list and "SALT" is in another, you get back "Salt".
Comparison is case-sensitive by default. To match values regardless of case, pass Comparer.OrdinalIgnoreCase as the second argument.
You can mix in more than two lists. The result keeps only values present in every single one.
The function only matches whole values as they are. If you need to clean or reshape items before comparing, run them through List.Transform first.
Duplicate values collapse. If a value appears twice in the first list but the result keeps it, you still get it once.

Common questions about List.Intersect

Should I use List.Intersect or List.Contains? Use List.Contains to test whether one single value sits in a list, which returns true or false. Use List.Intersect when you want the full set of values that two or more lists share.

How do I intersect more than two lists? Add every list inside the same outer braces, like List.Intersect({ListA,ListB,ListC}). The result keeps only values found in all of them.

List of All Power Query Functions

Related Power Query Functions / Articles: