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:

  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
  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




Written by SharePoint Librarian

I'm a SharePoint Business Analyst and Jayhawk from the Kansas City Area.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.