List.ContainsAny not working in List.Accumulate

[donkey shrek]

If I use Text.Contains to have 1 filter only, it works fine (e.g. only those containing "@").
However, I want to combine the filters into 1 query so there won't be so many steps. Therefore, I'm using List.ContainsAny instead.

I think it's because PQ cannot pick up List.Accumulate's "current" if you put it inside a list?

= List.Accumulate(Source[Email],
{},
(state, current) => if List.ContainsAny({current}, {"@", ".", "gmail"}) then List.InsertRange(state, List.Count(state), {current}) else state
)

Current Output:
Empty list

Source List:

 

[JGordon11]

In your code current is a text value. You convert it into a one element list by the curly braces {current}. Then you ask whether that one item list contains any of these values: "@", ".", "gmail". It will always return false unless that item is exactly one of those three text values (and none are so it returns an empty list).

I think you're really wanting to ask whether the text contains any of those values. There is Text.Contains function in M but not a Text.ContainsAny so maybe something like the following ( note that if you want it to be case sensitive then omit the Comparer.OrdinalIgnoreCase parameter of the Text.Contains function):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Text_ContainsAny = (txt as text, lst as list) as logical => List.Accumulate(lst, false, (s,c)=> if Text.Contains(txt, c, Comparer.OrdinalIgnoreCase) then true else s),
    lst = List.Select(Source[Email], each Text_ContainsAny(Text.From(_), {"@", ".com", "gmail"}))
in
    lst

Book1
	A	B	C	D
1	Email		Table1
2	xyz@gmail.com		xyz@gmail.com
3	xyz123		Dot.com
4	xyzabc		GMAIL
5	Dot.com
6	GMAIL
7	123456
8
Sheet1

 

[donkey shrek]

In your code current is a text value. You convert it into a one element list by the curly braces {current}. Then you ask whether that one item list contains any of these values: "@", ".", "gmail". It will always return false unless that item is exactly one of those three text values (and none are so it returns an empty list).

I think you're really wanting to ask whether the text contains any of those values. There is Text.Contains function in M but not a Text.ContainsAny so maybe something like the following ( note that if you want it to be case sensitive then omit the Comparer.OrdinalIgnoreCase parameter of the Text.Contains function):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Text_ContainsAny = (txt as text, lst as list) as logical => List.Accumulate(lst, false, (s,c)=> if Text.Contains(txt, c, Comparer.OrdinalIgnoreCase) then true else s),
    lst = List.Select(Source[Email], each Text_ContainsAny(Text.From(_), {"@", ".com", "gmail"}))
in
    lst

Book1
	A	B	C	D
1	Email		Table1
2	xyz@gmail.com		xyz@gmail.com
3	xyz123		Dot.com
4	xyzabc		GMAIL
5	Dot.com
6	GMAIL
7	123456
8
Sheet1

Thank you! I never knew about custom functions in PQ haha.
Could you explain why the 2nd parameter of List.Accumulate is false rather than a list?

 

[JGordon11]

The second parameter of List.Accumulate is the initial state. So I defaulted to false because no text contains has been found initially. State gets changed to true if text contains finds a match and otherwise state remains unchanged. So if any text contains is found the list accumulate will return true and if none are found it returns false. List.Accumulate has a somewhat misleading name because it doesn't necessarily return a list, it can return any data type - whatever the final state is.

Another looping function in M is List.Generate. It does always return a list.

List.Accumulate is roughly kin to vba For each x in collection ...do some stuff ... Next It always loops n times where n is the list count
List. Generate is roughly kin to Do while condition is true ... do some stuff and add the result as the next item of the list ... loop It loops an unspecified number of times until the condition is false. If not careful with List.Generate you can create an infinite loop.

 

[donkey shrek]

Thanks for the info! How would you modify this code to turn it into Text_ContainsAll instead of Any?

 

[donkey shrek]

Thanks for the info! How would you modify this code to turn it into Text_ContainsAll instead of Any?

Also, how do I output the values in the list that matched in Text_ContainsAny?

 

[donkey shrek]

Also, how do I output the values in the list that matched in Text_ContainsAny?

I was thinking something like this:

= let counter = 0 in Table.AddColumn(Source, "Domain Found", each if Text_ContainsAny([Email], RandomList) then [Email] & " " & List.PositionOf(RandomList, counter) and counter + 1 else "Not found")

not too sure if that code logic makes sense

 

[JGordon11]

Thanks for the info! How would you modify this code to turn it into Text_ContainsAll instead of Any?

Text_ContainsAll = (txt as text, lst as list) as logical => List.Accumulate(lst, true, (s,c)=> if not Text.Contains(txt, c, Comparer.OrdinalIgnoreCase) then false else s)

 

[donkey shrek]

Text_ContainsAll = (txt as text, lst as list) as logical => List.Accumulate(lst, true, (s,c)=> if not Text.Contains(txt, c, Comparer.OrdinalIgnoreCase) then false else s)

How do I return the values from the list that have been matched?