List.Difference - Function | Power Query How

List.Difference is a Power Query M function that finds items in one list that do not appear in another list, supporting duplicates and optional equation criteria for equality testing. The function returns a list of the unique items from the first list.

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

Syntax

List.Difference(
   list1 as list,
   list2 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.Difference function identifies elements that are present in one list but not in another. It takes two lists as input and returns a new list containing items that are unique to the first list when compared to the second.

The function also accommodates duplicate values, meaning if an item appears multiple times in the first list and is not in the second list, all instances will be included in the output. Additionally, you can use an optional parameter called equationCriteria to fine-tune how the function compares items for equality, such as case sensitivity or culture-specific rules.

Examples

Let’s dive into some practical examples to better understand the List.Difference function.

Identifying Unique Numbers in a List

Let’s say you have a list of integers from 1 to 5 and you’re curious to know which of these numbers are not included in another list that has the numbers 3, 4, and 5. Here’s how you can find out:

// Output: {1, 2}
List.Difference( {1, 2, 3, 4, 5}, {4, 5, 3} )

In this example, the function returns {1, 2}, which are the unique numbers in the first list that don’t appear in the second list.

When Lists Have No Unique Elements

What happens when all the items in your first list are also in the second list? Let’s see:

// Output: {}
List.Difference( {1, 2}, {1, 2, 3} )

In this case, the function returns an empty list, {}, signifying that the first list doesn’t contain any elements that are absent in the second list.

Returning Duplicate Values

The List.Difference function can also handle duplicate values. For instance, let’s say the number 1 appears multiple times in your first list but not in your second list:

// Output: {1,1,1}
List.Difference ( {1,1,1,2}, {2,3,4})

Here, the function returns all instances of the number 1, showcasing its ability to handle duplicates.

Comparing Columns in Different Tables

Let’s say you’re working with multiple tables and you want to identify which columns are unique to one table when compared to another. This is particularly useful when you’re merging tables and want to avoid duplicate columns.

List.Difference( 
  Table.ColumnNames( Table1 ), 
  Table.ColumnNames( Table2 )
)

In this example, the function compares the column names of Table1 and Table2 and returns a list of column names that are unique to Table1.

Case-Insensitive Comparisons

If you need to make a case-insensitive comparison, you can employ the Comparer.OrdinalIgnoreCase function.

// Output: { "cherry" }
List.Difference( 
  {"apple", "banana", "cherry"}, 
  {"APPLE", "BANANA"}, 
  Comparer.OrdinalIgnoreCase
)

Here, the function identifies “cherry” as unique, even though the other fruits are spelled differently in terms of case.

Culture-Specific Comparisons

Sometimes, you may need to make comparisons that are sensitive to cultural differences, particularly when dealing with special characters in strings.

// Output: { "Færdig" }
List.Difference( 
  {"Færdig"}, 
  {"Faerdig"}, 
  Comparer.FromCulture("da-DK")
)

The same comparison using English comparison rules shows us a different result.

// Output: { }
List.Difference( 
  {"Færdig"}, 
  {"Faerdig"}, 
  Comparer.FromCulture("en-US")
)

In summary, the List.Difference function is useful for identifying unique elements across lists, whether you’re dealing with numbers, text, or even table columns. Its flexibility extends to case and culture-sensitive scenarios, making it a useful function for data manipulation and analysis.

Learn more about List.Difference 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
Remove List Items by Positions in Power Query
This article shows how you can remove items from a list by their index position by using a custom function. » Read more

Other functions related to List.Difference are:

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