List.IsDistinct

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will explore the M code behind the List.IsDistinct function and how it works.

Understanding Lists in Power Query

Before we dive into the List.IsDistinct function, we must first understand what a list is in Power Query. In Power Query, a list is a collection of values of the same data type. Lists can be created manually by typing in values or by using other functions to generate them.

For example, the following M code creates a list of numbers from 1 to 10:

let

myList = {1,2,3,4,5,6,7,8,9,10}

in

myList

Lists can also be created from columns in a table. For example, the following M code creates a list of unique values from a column in a table:

let

Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],

myList = List.Distinct(Source[Column1])

in

myList

The List.IsDistinct Function

The List.IsDistinct function is used to check if a list has distinct values or not. It takes a list as its input and returns a Boolean value (true or false) indicating whether the list has distinct values or not.

The syntax for the List.IsDistinct function is as follows:

List.IsDistinct(list as list) as logical

For example, the following M code checks if a list has distinct values:

let

myList = {1,2,3,4,5,6,7,8,9,10},

isDistinct = List.IsDistinct(myList)

in

isDistinct

In this example, the List.IsDistinct function returns true because all values in the list are distinct.

How List.IsDistinct Works

The List.IsDistinct function works by comparing each value in the list with every other value in the list. If any two values are the same, then the function returns false. If all values are distinct, then the function returns true.

For example, the following M code creates a list with duplicate values and checks if it has distinct values:

let

myList = {1,2,3,4,5,6,7,8,9,10,5},

isDistinct = List.IsDistinct(myList)

in

isDistinct

In this example, the List.IsDistinct function returns false because there is a duplicate value (5) in the list.

Using List.IsDistinct in Data Transformations

The List.IsDistinct function can be used in various data transformation scenarios. For example, it can be used to:

- Remove duplicate values from a list

- Check if a list has unique values before merging with another table

- Identify duplicate records in a table

For example, the following M code removes duplicate values from a list:

let

myList = {1,2,3,4,5,6,7,8,9,10,5},

distinctList = List.Distinct(myList)

in

distinctList

In this example, the List.Distinct function is used to remove the duplicate value (5) from the list.

In conclusion, the List.IsDistinct function in Power Query is a useful tool for checking if a list has distinct values or not. It can be used in various data transformation scenarios to remove duplicate values from a list, check if a list has unique values before merging with another table, or identify duplicate records in a table. Understanding the M code behind the List.IsDistinct function can help users to leverage its power in their data transformation 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)