In this example, we define a list called myList that contains the values 1 through 5. We then use the List.Contains function to search for the value 3 in myList. The result of the function will be true, since the value 3 is present in the list.

The M Code Behind List.Contains

The actual M code behind the List.Contains function is quite simple. Here is the code:

Let's break down the code line by line:

- `List.Contains = (list as list, value as any) as logical =>` - This line defines the function and its arguments. The List.Contains function takes two arguments: `list` (the list to be searched) and `value` (the value to be found).

- `if List.IsEmpty(list) then false` - This line checks if the list is empty. If the list is empty, the function returns false.

- `else if list{0} = value then true` - This line checks if the first item in the list is equal to the value being searched for. If it is, the function returns true.

- `else List.Contains(List.RemoveFirstN(list, 1), value)` - This line calls the List.Contains function recursively, passing in a modified version of the original list. The List.RemoveFirstN function is used to remove the first item from the list, so that the function can search for the value in the remaining items.

Tips for Using List.Contains

Here are some tips for using the List.Contains function effectively in Power Query:

1. Be mindful of the data types in your list. The List.Contains function will only work if the data types match between the list items and the value being searched for.

2. Use the List.Transform function to apply the List.Contains function to multiple lists at once. This can be useful when you need to search for a specific value across multiple columns or tables.

3. Combine the List.Contains function with the List.Select function to filter a list based on a specific value. This can be useful when you need to extract specific rows from a large data set.

The List.Contains function is a useful tool in Power Query for searching for specific values in a list. Understanding the M code behind this function can help you use it more effectively in your data transformation and analysis tasks. By following the tips outlined in this article, you can make the most of this powerful function and streamline your data processing workflows.

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

Upcoming Courses

Contact Us