# List.Difference

Updated on

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``````

## 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.