List.Intersect Function in Power Query

This function identifies the common elements present in two or more lists.

In this article, I will demonstrate the basic applications of the List.Intersect function.

Lstintersect.xlsx

Download XLSX • 17KB

Example-1:

The source data includes animal names in two separate lists.

Our goal is to identify the items that appear in both lists.

let

 Lst1 = Excel.CurrentWorkbook(){[Name="Table4"]}[Content][Animals1],

 Lst2 = Excel.CurrentWorkbook(){[Name="Table5"]}[Content][Animals2],

 Result = List.Intersect({Lst1,Lst2})

in

 Result

Example-2:

Our source data contains 2 lists. Both are list within list structure.

Our aim is to extract common list between these lists.

let

 Lst1 = List.Split(Excel.CurrentWorkbook(){[Name="Table4"]}[Content][Animals1],2),

 Lst2 = List.Split(Excel.CurrentWorkbook(){[Name="Table5"]}[Content][Animals2],2),

 Result = List.Intersect({Lst1,Lst2})

in

 Result

Example-3:

Our source data includes two lists. The first is a nested list where each inner list contains two elements. The second is a list with a single element.

The goal is to identify the common list items between these two lists. The basic List.intersect function fails to detect any matches. In such cases, we can apply the equationcriteria argument.

let

 Lst1 = { { "A","South" },{"B", "North"},{"D", "East"} },

 Lst2 = { "South" },

 Result = List.Intersect({Lst1,{Lst2}}, each List.Last(_))

in

 Result