topic Re: Count number of elements in each column cell (Delimiters) and display the values in the new column? in DAX Commands and Tips

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

ssspk — Tue, 26 Sep 2023 17:04:15 GMT

Hi,

Having a column like below,

Data:

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

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:

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

Thank you.

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

Anonymous — Tue, 26 Dec 2023 02:16:15 GMT

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.

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

ssspk — Thu, 28 Sep 2023 16:43:49 GMT

Hi Anonymous ,

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