List.Intersect

We can use List.Intersect to find the common elements:

This will return a new list containing the values 4 and 5.

How Does List.Intersect Work?

List.Intersect works by comparing each element in the first list with each element in the second list. If an element is found in both lists, it is added to the new list that is returned by the function.

The M code behind List.Intersect is relatively simple. Here's what it looks like:

Let's break down what's happening here. The function takes two lists as input - List1 and List2. The List.Intersect function is then used to find the common elements between the two lists.

The List.Distinct function is then used to remove any duplicates from the resulting list. This is important, because List.Intersect can sometimes return duplicate values if there are duplicates in the input lists.

Finally, the Intersection variable is returned as the output of the function.

Using List.Intersect in Practice

Now that we understand how List.Intersect works, let's take a look at some real-world examples of how it can be used.

Example 1 - Comparing Data from Two Excel Spreadsheets

Suppose you have two Excel spreadsheets - one containing customer data, and another containing order data. You want to find out which customers have placed orders, but you don't want to manually compare the two spreadsheets.

Here's how you can use List.Intersect to do this:

1. Load both spreadsheets into Power Query.

2. Use the "Remove Other Columns" function to remove any columns that aren't relevant.

3. Use the "Remove Duplicates" function to remove any duplicate rows.

4. Use List.Intersect to find the common values between the "Customer Name" column in the customer data spreadsheet and the "Customer Name" column in the order data spreadsheet.

5. The resulting list will contain the names of all customers who have placed orders.

Example 2 - Comparing Data from a Database and an Excel Spreadsheet

Suppose you have a database containing customer data, and an Excel spreadsheet containing sales data. You want to find out which customers have made purchases, but you don't want to manually compare the two sources.

Here's how you can use List.Intersect to do this:

1. Load the customer data from the database into Power Query.

2. Load the sales data from the Excel spreadsheet into Power Query.

3. Use the "Remove Other Columns" function to remove any columns that aren't relevant.

4. Use List.Intersect to find the common values between the "Customer ID" column in the customer data and the "Customer ID" column in the sales data.

5. The resulting list will contain the IDs of all customers who have made purchases.

List.Intersect is a powerful function in Power Query that allows you to find the common elements between two lists. In this article, we've explored the M code behind List.Intersect, and shown how it can be used in practice to compare data from different sources.

By mastering List.Intersect, you can save yourself a lot of time and effort when working with data in Power Query.

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

Upcoming Courses

Contact Us