List.Union - List Function

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

Argument	Attribute	Description
lists		A list of lists to combine.
equationCriteria	optional	Uses Comparer Functions to determine how values are equated during operations. Options include Comparer.Ordinal for exact case-sensitive matching, Comparer.OrdinalIgnoreCase for case-insensitive matching, and Comparer.FromCulture for culture-specific comparisons.

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"}.

Learn more about List.Union 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.Union are:

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