Advertisements

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
    PBIDesktop_2018-09-28_10-59-05.png
  2. Add a custom column
    PBIDesktop_2018-09-28_10-59-18.png
  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

2018-09-28_10-46-40.png

 

Advertisements

1 Comments

Leave a Reply

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

%d bloggers like this: