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:

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

1. Open Query Editor
   
2. Add a custom column
   
3. 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
4. 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