List.Difference

D

Back to top

T

Back to top

#

Back to top

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

One of the functions available in M is List.Difference, which allows you to compare two lists and return the items that are unique to each list. In this article, we will explore the M code behind the List.Difference function and how it can be used in Power Query.

Understanding the List.Difference Function

The List.Difference function takes two lists as inputs and returns a new list that contains the items that are unique to each list. In other words, it returns the items that are in one list but not in the other.

Here is the syntax for the List.Difference function:

List.Difference(list1 as list, list2 as list) as list

The first argument, list1, is the list that you want to compare to the second list, list2. The second argument, list2, is the list that you want to compare to list1. The function then returns a new list that contains the items that are unique to each list.

Example Usage

Let's take a look at an example of how the List.Difference function can be used in Power Query. Suppose we have two lists, list1 and list2, as shown below:

list1 = {“apple”, “banana”, “orange”, “pear”}

list2 = {“banana”, “kiwi”, “orange”, “grape”}

We can use the List.Difference function to compare these two lists and return the items that are unique to each list. Here is the M code to do this:

let

list1 = {“apple”, “banana”, “orange”, “pear”},

list2 = {“banana”, “kiwi”, “orange”, “grape”},

result = List.Difference(list1, list2)

in

result

The result of this code will be a new list that contains the items that are unique to each list:

{“apple”, “pear”, “kiwi”, “grape”}

How List.Difference Works

To understand how the List.Difference function works, let's take a closer look at the M code behind it. Here is the M code for the List.Difference function:

let

List.Difference = (list1 as list, list2 as list) =>

let

removeItems = (listToCheck as list, listToRemove as list) =>

List.RemoveMatchingItems(listToCheck, listToRemove),

result = List.Combine({removeItems(list1, list2), removeItems(list2, list1)})

in

result

in

List.Difference

The List.Difference function is defined as a lambda function that takes two arguments, list1 and list2. The function then defines another function called removeItems that takes two lists as inputs and removes the items in the second list from the first list.

The List.Combine function is then used to combine the results of removing the items from each list. This results in a new list that contains only the items that are unique to each list.

The List.Difference function in Power Query is a powerful tool that allows you to compare two lists and return the items that are unique to each list. The M code behind this function is relatively simple, but it can be used to perform complex data transformations in Power Query. By understanding how this function works, you can use it to manipulate data more effectively in Power Query.

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)