Count number of elements in each column cell (Delimiters) and display the values in the new column?

[ssspk]

Hi,

Having a column like below,

Data:

"[""bTravel"",""bTravel Planning"",""travel booking"",""travel planning""]"
[]
"[""engine"",""news"",""search""]"
[]
"[""collaboration"",""connect"",""connections"",""network""]"

Column Data

To get the count of the words in each cell using a delimiter comma(,).

Used fx as below for the specific count of keywords,

New Column name is keywords count.

= Table.AddColumn(applicationcatalog_Table, "keywords count", each List.NonNullCount(Text.Split([keywords],",")))

Result:

Result of Count

Why its displaying resuit as 1 in cell, instead of 0 as result for []  in the coulun(keywords count) cells.?

Thank you.

[v-tangjie-msft · Accepted solution]

Hi @ssspk ,

The list after separating by "," is not empty, so the function works as designed, the solution is

You can add another column:

= Table.AddColumn(#"Added Custom", "Custom", each if [keywords] = "[]" then 0 else if [keywords] <> "[]" then [keywords count] else null)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[ssspk]

Hi @v-tangjie-msft ,

Thank you for your time and solution. Its working fine.