List.Intersect - List Function

List.Intersect is a Power Query M function that finds the intersection of values in the input lists. The function returns a list with the common values, with an optional equationCriteria parameter.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
List		A list containing various sub-lists. The function finds the common elements across multiple lists.
Equation Criteria	Optional	Sets the rules for comparing list values, such as case-insensitive matching with Comparer.OrdinalIgnoreCase or culture-specific comparisons using Comparer.FromCulture. If not specified, it defaults to Comparer.Ordinal.

Description

The List.Intersect function is designed to find the common elements across multiple lists. It returns a new list containing these shared elements. The function also allows for optional parameters to specify the type of comparison, such as case-sensitive, case-insensitive, or even culture-specific comparisons.

Examples

Let’s delve into some real-world examples to better understand the List.Intersect function.

Common Elements in Numerical Ranges

Imagine you have three numerical ranges: 1 to 5, 2 to 6, and 3 to 7. You want to find the numbers that appear in all three ranges. The List.Intersect function makes this task simple.

// Output: {3, 4, 5}
List.Intersect( {{1..5}, {2..6}, {3..7}} )

Overlapping Column Names between Tables

In another scenario, you might have multiple tables and you’re interested in identifying the columns that are common to all of them. The following expression accomplishes this by comparing the column names of three different tables.

List.Intersect( 
{ Table.ColumnNames( Table1 ), 
  Table.ColumnNames( Table2 ), 
  Table.ColumnNames( Table3 ) } 
)

Shared ingredients across Recipes

Let’s say you’re planning meals for the week and want to find common ingredients across different recipes. This can help you in efficient grocery shopping.

// Output: {"Salt", "Water"}
List.Intersect( 
{ {"Salt", "Pepper", "Water"}, 
  {"Salt", "Sugar", "Water"}, 
  {"Salt", "Oil", "Water"} } 
)

Case-Sensitive Comparisons

By default, List.Intersect performs a case-sensitive comparison using Comparer.Ordinal. So, if your data contains mixed casing, the function will treat them as distinct values.

// Output: {}
List.Intersect( 
{ {"Salt", "Pepper", "Water"}, 
  {"SALT", "sUGar", "water"}, 
  {"Salt", "Oil", "WATER"} } 
)

Ignoring Case Sensitivity

However, List.Intersect supports an optional equation criteria. By instructing it to compare values using Comparer.OrdinalIgnoreCase the function ignores case.

// Output: { "Salt", "Water" }
List.Intersect( 
  { {"Salt", "Pepper", "Water"}, 
    {"SALT", "sUGar", "water"}, 
    {"Salt", "Oil", "WATER"} },
  Comparer.OrdinalIgnoreCase
)

By ignoring capitalization, the operation returns “Salt” and “Water”. The returned values contain the casing of the first time the words occurred.

Culture-Sensitive Comparisons

In situations where the comparison should be culture aware, you can introduce the Comparer.FromCulture function and specify a culture.

// Output: { "Færdig", "Water" }
List.Intersect( 
  { {"Færdig", "Pepper", "Water"}, 
    { "Faerdig", "sUGar", "water"}, 
    {"Salt", "FAERDIG", "WATER"} },
  Comparer.FromCulture( "en-US", true ) )

Other cultures may treat these values differently. For example, in Danish “æ” is different from “ae”.

// Output: { "Water" }
List.Intersect( 
  { {"Færdig", "Pepper", "Water"}, 
    { "Faerdig", "sUGar", "water"}, 
    {"Salt", "FAERDIG", "WATER"} },
  Comparer.FromCulture( "en-US", true ) )

Learn more about List.Intersect in the following articles:

Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more

Other functions related to List.Intersect are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-intersect