Count No of Text in a cells

[Aradhika]

Hi,

I am new to Power Bi and learning DAX.
Well, i have scenario below:

Table1
Column A Results
BP,SUGAR,DIABETES,HYPERTENSION 4
BP,SUGAR,DIABETES 3

Here, i want to count no of words(BP,SUGAR,DIABETES) are there in a cell.

Please help...

Thanks in advance
Ravi

 

[Aradhika]

Hi,

I am new to Power Bi and learning DAX.
Well, i have scenario below:

Table1
Column A Results
BP,SUGAR,DIABETES,HYPERTENSION | 4
BP,SUGAR,DIABETES | 3

Here, i want to count no of words(BP,SUGAR,DIABETES) are there in a cell.

Please help...

Thanks in advance
Ravi

 

[JustynaMK]

Hi Aradhika,

You can try an old Excel trick for this DAX formula:

CountWords = LEN(Table1[Column A]) - LEN(SUBSTITUTE(Table1[Column A], ",", "")) + 1

The main part is counting how many times a comma (",") appears in a word, then we need to add +1 at the end of the formula to return the actual number of words.

 

[sandy666]

with Power Query and without duplicating worksheet formula
SplitCount = Table.AddColumn(Source, "SCount", each List.Count(Text.Split([raw],",")))

raw	SCount
BP,SUGAR,DIABETES,HYPERTENSION	4
BP,SUGAR,DIABETES	3

simply add Custom Column, name it (here: SCount) and use: List.Count(Text.Split([raw],",")) inside

 

[Aradhika]

Thank you very much.... JustynaMk & Sandy666

I have used above code and implemented in my project ...

Once again thanks, for your prompt reply and efforts.
Aradhika

 

[sandy666]

You are welcome