How to get a simple column of text length as wordcount

[Anonymous]

Hi!

I am trying to add a column to my data to give a wordcount of the text string on each row.

Eg:

Text	Wordcount
Brief comment	2
Very long rambling paragraphs of text	6

I currently do this in excel with the formula '=LEN(C2)-LEN(SUBSTITUTE(C2," ",""))+1' [text string being in C2] but ideally want to make this part of the data transformation to avoid having to constantly mess with editing in multiple places.

My online searching for an existing answer is turning up all sorts of complicated code analysing frequencies of unique words etc, but all I need is a simple count per row.

[adudani · Accepted solution]

hi @Anonymous ,

create a blank query in the power query editor

copy and paste the below code into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcirKTE1TSM7PzU3NK1HSUTJSitWJVgpLLapUyMnPS1coSsxNyskEMgoSixLTixILMooV8tMUSlIrQKrNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t, Wordcount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}, {"Wordcount", Int64.Type}}),
    Textwithspace = Table.AddColumn(#"Changed Type", "Textwithspace", each Text.Length([Text]), Int64.Type),
    Textwithoutspace = Table.ReplaceValue(Table.AddColumn(Textwithspace, "Textwithoutspace", each [Text])," ","",Replacer.ReplaceText,{"Textwithoutspace"}),
    Lengthoftextwithoutspace = Table.TransformColumns(Textwithoutspace,{{"Textwithoutspace", Text.Length, Int64.Type}}),
    #"Added Custom" = Table.AddColumn(Lengthoftextwithoutspace, "WORDCOUNT.1", each ([Textwithspace]-[Textwithoutspace])+1),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Text", "WORDCOUNT.1"})
in
    #"Removed Other Columns"

Steps taken

1. Extracted the length of the [text] column

2. created a new column = text. Replaced  " " with  "". Extracted the text length.

3. Subtracted the step1 and step2 output plus 1 in "Wordcount.1"

4. removed all other rows.

This can be done in 2 steps IMO. Have showed all steps for understanding.

[ppm1 · Accepted solution]

This is easier to do in the query editor with a custom column with the expression below.

= List.Count(Text.Split([TextColumn], " "))

Pat

[adudani · Accepted solution]

hi @Anonymous ,

create a blank query in the power query editor

copy and paste the below code into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcirKTE1TSM7PzU3NK1HSUTJSitWJVgpLLapUyMnPS1coSsxNyskEMgoSixLTixILMooV8tMUSlIrQKrNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t, Wordcount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}, {"Wordcount", Int64.Type}}),
    Textwithspace = Table.AddColumn(#"Changed Type", "Textwithspace", each Text.Length([Text]), Int64.Type),
    Textwithoutspace = Table.ReplaceValue(Table.AddColumn(Textwithspace, "Textwithoutspace", each [Text])," ","",Replacer.ReplaceText,{"Textwithoutspace"}),
    Lengthoftextwithoutspace = Table.TransformColumns(Textwithoutspace,{{"Textwithoutspace", Text.Length, Int64.Type}}),
    #"Added Custom" = Table.AddColumn(Lengthoftextwithoutspace, "WORDCOUNT.1", each ([Textwithspace]-[Textwithoutspace])+1),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Text", "WORDCOUNT.1"})
in
    #"Removed Other Columns"

Steps taken

1. Extracted the length of the [text] column

2. created a new column = text. Replaced  " " with  "". Extracted the text length.

3. Subtracted the step1 and step2 output plus 1 in "Wordcount.1"

4. removed all other rows.

This can be done in 2 steps IMO. Have showed all steps for understanding.