count the number of words within a cell & to exclude comma and others as a delimeters

[Anonymous]

Hello Team,

I am looking to count the number of words within a cell & to exclude comma and other as a delimeters. Would be happier to learn, if there is simple way to do this or please let me know where i am going wrong with the below DAX query.

Column Text Example:

AlbertEinstein,SumanRao,AlexanderGrahambell,Sushma

I tried doing this:

List.Count(List.RemoveItems(Text.SplitAny([Cuisines]," #(tab)#(lf)"),{","}))

Output:

TExt split function works perfectly fine but "comma" is not getting ignored, its being counted as a extra character along with word count! 

Please help!!

[parry2k]

@Anonymous M code you used is different than what I provided. Why do you have brackets around the "[Cuisines]" column? I never used these brackets when I provided the expression. Can you make it exactly what I provided, just change the step and column name, don't do anything extra.

✨ Follow us on LinkedIn and subscribe to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[AlexisOlson]

How about splitting on any of those delimiters and counting non-empty list items?

List.Count(List.RemoveItems(Text.SplitAny([Cuisines], " ," & "#(tab)" & "#(lf)"), {""}))

[Anonymous]

Apologies to be replying late!!

Tried above, no luck.. below is screen shot of the error. Let me know, if i am missing something!!Error Page

[AlexisOlson · Accepted solution]

It's not clear to me what you're trying to do here. The code I provided does not throw this error when referencing a text column.

[Anonymous]

AlexisOlson,

I got it working. Thank you much! 

[parry2k]

@Anonymous try adding a new custom column using following M code

Table.ColumnCount(Table.SplitColumn( #"Changed Type", "Col1",Splitter.SplitTextByAnyDelimiter({" #(tab)#(lf)", ","})))

Make sure change #"Changed Type" step to the previous step and "Col1" is the name of the column you wan to split.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Apologies for replying late!!

Below is the screen shots of the message.. Let me know, if am missing out on something ..
Replaced - "Changed Type" & "COL1" to real values, as seen in the screen shot