Count Number of Words – Power Query – Tips & Tricks 1

Many a time, you are required to count number of Words in a record. The classic formula in Excel happens to be

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

Note – If you have blank cells also, then right formula is

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+(LEN(A2)<>0)

To calculate, you can always mimic the above in a custom column in PQ by following

= Text.Length([Words List])-Text.Length(Text.Replace([Words List]," ",""))+1

But you should never try to mimic Excel functions in Power Query if there is a built-in way to do this. Power Query built-in way is always more efficient. The Power Query way to count words is through following

List.Count(Text.Split([Words List]," "))

The Excel file having PQ for Count Number of Words can be downloaded from PO_TT1. Power Query M function reference – PowerQuery M | Microsoft Docs