A

B

C

D

E

F

G

H

I

J

L

#

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}

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])

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)

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)

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)

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)
   	Power BI Training for Facility Management Professionals (2 Days)
 	£1,990.00 Add to basketBuy Now
 
 
 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)