# List.Union

Updated on

List.Union is a Power Query M function that combines the items from multiple input lists into a single output list, maintaining traditional bag semantics. The function returns a list containing all items from any of the input lists.

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

## Syntax

``````List.Union(
lists as list,
optional equationCriteria as any,
) as list``````

## Description

The `List.Union` function allows users to merge multiple lists, eliminating any repeated values, and returning a single list with distinct elements.

An optional equation criteria value,Â `equationCriteria`, can be specified to control equality testing. It offers capabilities like case-sensitive and case-insensitive comparisons, as well as cultural-specific comparisons.

## Examples

Let’s explore the List.Union function with some well-illustrated examples.

### Merging Two Basic Lists

Imagine you have two lists of numbers: `{1, 2, 3}` and `{2, 3, 4}`. How can they be merged into one? Here’s how:

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

This returns a unified list, `{1, 2, 3, 4}`, effectively removing duplicates.

### Merging Several Lists Together

Now, consider a scenario where multiple lists like `{1..5}`, `{2..6}`, and `{3..7}` need to be unified. The M language elegantly handles this:

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

The result is a comprehensive list: `{1, 2, 3, 4, 5, 6, 7}`, capturing all unique numbers across the three lists.

### Handling Lists with Repeated Values

In real-life datasets, you might encounter repeated values. The beauty of `List.Union` is that it ensures each value is represented only once:

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

However, be aware that this behaviour only happens when including the values from new lists.

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

In the above example, the first list already contains the value 1 twice, and the second list contains the number 3 twice. In those cases, they are still returned. Yet the formula makes sure the number 2 does not appear twice.

### Case Sensitive Comparison

When working with textual data, understanding the distinctions between cases is crucial. The `Comparer.Ordinal` method is an effective tool for this, as it treats different letter cases as distinct:

``````// Output: { "apple", "APPLE", "banana", "BANANA" }
List.Union( { { "apple", "banana" }, { "APPLE", "BANANA" } }, Comparer.Ordinal )``````

It treats “apple” and “APPLE” just as “banana” and “BANANA” as separate entities, giving an output: `{"apple", "banana", "APPLE", "BANANA"}`.

### Ignore Case using Comparer.OrdinalIgnoreCase

Sometimes, case distinctions might not be essential. In such cases, `Comparer.OrdinalIgnoreCase` comes to the rescue, merging lists without considering letter cases:

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

This function treats “apple” and “APPLE” as identical, producing: `{"apple", "banana"}`.

### Consider Cultural Differences

Languages and cultures have unique sorting rules. Using `Comparer.FromCulture`, you can honor these nuances, as shown in the example involving the string “FÃ¦rdig”:

``````// Output: {"FÃ¦rdig", "FÃ†rdig"}
List.Union({{"FÃ¦rdig", "FÃ†rdig" }, {"Faerdig"}}, Comparer.FromCulture("en-US", true ))``````

It recognizes the cultural differences in sorting, hence the output: `{"FÃ¦rdig", "FÃ†rdig"}`.