List.Distinct

D

Back to top

T

Back to top

#

Back to top

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

Overview of List.Distinct

The List.Distinct function is part of the M language, which is the programming language used in Power Query. The function takes a list as its input and returns a new list with only the unique values from the original list. For example, if you have a column with the following values:

| Column A |

|———-|

| Blue |

| Red |

| Green |

| Blue |

Using List.Distinct on this column would return a new column with the values:

| Column A |

|———-|

| Blue |

| Red |

| Green |

The M Code Behind List.Distinct

The M code for the List.Distinct function is relatively simple. It consists of a single line of code that specifies the input list and the output list. Here is the basic syntax for List.Distinct:

List.Distinct(list as list) as list

The "list as list" parameter specifies the input list, while the "as list" at the end of the function specifies that the output should be a list. To use the List.Distinct function, you would replace "list" with the name of the column or list you want to retrieve unique values from.

For example, if you have a table called "Sales" with a column called "Region", you could use List.Distinct to retrieve a list of unique regions:

List.Distinct(Sales[Region])

The output of this code would be a new list with only the unique regions from the "Region" column.

Customizing List.Distinct

While List.Distinct is a useful function on its own, it can also be customized to fit specific data queries. One way to customize List.Distinct is to use the optional "comparer" parameter. This parameter specifies the comparison method used to determine if two values are equal. By default, List.Distinct uses a case-sensitive comparison method, which means that "Blue" and "blue" would be treated as separate values. However, you can customize the comparison method to ignore case, or even use a custom comparison function.

Here is the syntax for using the "comparer" parameter in List.Distinct:

List.Distinct(list as list, optional comparer as any) as list

To use the "comparer" parameter, you would specify the comparison method in the second parameter. For example, to use a case-insensitive comparison method, you could use the following code:

List.Distinct(Sales[Region], Comparer.OrdinalIgnoreCase)

This would return a list of unique regions, ignoring case.

The List.Distinct function is a useful tool for retrieving unique values from a list or column in Power Query. Understanding the M code behind the function can help users customize their data queries for more efficient and accurate results. By using the optional "comparer" parameter, users can further customize the function to fit their specific needs.

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)