How to count per-row occurrence of multiple words or phrases across multiple columns in Power BI

Sometimes you want to know how many times words and phrases appear across multiple columns. For example, if we want to know how many times “Cat” OR “Mouse” appears in each row, we would get:

Column 1	Column 2	Column 3	Word Count
Cat Yarn	Mouse	Cat Dog	3
Dog Cat	Horse Hay	Dog	1
Cat Cat	Cat	Mouse	4

Here’s how to build a custom column that will give you that count:

Open Query Editor
Add a custom column
Name it (i.e. Word Count) and paste the following formula, adjusting it to fix the columns you want included, and the words you want searched:
- let
  String = [Column 1] & [Column 2] & [Column 3],
  Count = List.Count(Text.Split(String,”Word 1″))-1+List.Count(Text.Split(String,”Word 2″))-1+List.Count(Text.Split(String,”Word 3″))-1
  in
  Count
Click OK

For example:

let
String = [City] & [Location] & [Customer] & [Order],
Count = List.Count(Text.Split(String,”Cat”))-1+List.Count(Text.Split(String,”Mouse”))-1
in
Count