List.Union

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.Union

Syntax of List.Union

The List.Union function takes one or more lists as input and combines them into a single list. The syntax of List.Union function is as follows:

List.Union(list1 as list, list2 as list, …, optional comparer as any) as list

The function takes one or more lists as input, separated by commas. You can combine two or more lists by specifying their names. The optional comparer parameter is a function that compares elements in the list. If you omit the comparer parameter, List.Union uses the default equality comparer to compare elements in the list.

Usage of List.Union

You can use List.Union to combine two or more lists into a single list. The function returns a new list that contains all the distinct elements from the input lists. For example, suppose you have two lists, A and B, with the following elements:

A = {1, 2, 3, 4}

B = {3, 4, 5, 6}

You can use the List.Union function to combine the two lists and get a new list, C, with all the distinct elements from A and B:

C = List.Union(A, B)

The resulting list, C, will have the following elements:

C = {1, 2, 3, 4, 5, 6}

You can also use the comparer parameter to specify a custom comparer function. The comparer function takes two arguments and returns true if the two arguments are equal or false if they are not equal. For example, suppose you have two lists, A and B, with the following elements:

A = {“apple”, “banana”, “cherry”}

B = {“APPLE”, “BANANA”, “cherry”}

You can use the comparer parameter to compare the elements in the list case-insensitively:

C = List.Union(A, B, (x, y) => Text.Lower(x) = Text.Lower(y))

The resulting list, C, will have the following elements:

C = {“apple”, “banana”, “cherry”}

M Code Behind List.Union

The M code behind the List.Union function is relatively simple. The function first checks if there are any lists to combine. If there are no lists, the function returns an empty list. If there is only one list, the function returns the same list.

List.Union = (list1 as list, list2 as list, …, optional comparer as any) as list =>

let

combinedList = List.Combine({list1, list2, …}),

distinctList = List.Distinct(combinedList, comparer)

in

distinctList

The List.Combine function combines all the input lists into a single list. The List.Distinct function removes any duplicate elements from the combined list. Finally, the function returns the distinct list.

The List.Union function in Power Query is a useful tool for combining multiple lists into a single list. By understanding the M code behind the List.Union function and its syntax and usage, you can leverage its power to simplify data transformation and analysis tasks.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)