Power BI Functions — (List.Contains, List.ContainsAny, List.ContainsAll) | by Peter Hui | Nov, 2020


Let’s walk through an example.

I have a table here in power query and I will use the add columns feature to add in additional columns to use our contain functions.

Image by author

Let’s add the first column to use List.Contains using the add columns button.

The result will be this column here.

Let’s create our first column. I want to check if the Store_Number 1 is anywhere in the column.

Here the column indicates that there is a store number 1 in that column of store number.

Image by author

I also want to check for if there is a store number 1 and also, any reviews contains the word “Great”.

Image by author

Final column, I want to check if all the Review criteria I have — {“Great”,”Good”,”Meh”} — are represented in the column.

Image by author

This particular one — List.ContainsAll is a little bit different than the others.

If you are doing a custom column just like the others, it wouldn’t work because of the row context in Power Query.

Intuitively if you tried doing —


Will actually return FALSE, even though the items you have listed here are all represented in the column.

The issue here is the row — if you just refer to the column, Power Query takes the “cell” of the column and checks it against the list of {“Great”,”Good”,”Meh”} and returns FALSE. That particular “cell” does not contain all the items of that list so it return FALSE.

When we had used List.ContainsAny, List.Contains, they returned the right value because even if it is doing a check by each value it will still get us what we want. The difference here is we want the entire column to be nested in a “cell” before comparing to the list.

This is why we need to write.

List.ContainsAll( #PreviousStep[Reviews],{“Great”, ”Good”, “Meh”} )

Here it is referring the entire column as a list — to check against another list.

The result will return TRUE


Source link

Write a comment