Count how many delimiters there are in cell.

[Anonymous]

I have a bunch of data I would like to assign a level of detail to.  The data has pipes between each "level".

How can I count the number of "|" and assign a level to them?

Thank you!

For example

TTL COFFEE	Level 1 ( No | in string )
TTL COFFEE | N-RTD COFFEE	Level 2 ( One | in string )
TTL COFFEE | N-RTD COFFEE | 12OZ	Level 3 ( Two | in string )

[AlexisOlson]

In the query editor M language, you could try one of these options to count the pipes:

Text.Length(Text.Select([Column],{"|"}))

List.Count(Text.Split([Column], "|"))

If you're doing this in DAX, you can use the old Excel trick of replacing the pipes with an empty string and counting how much shorter your text is.

PipeCount =
VAR Str = SELECTEDVALUE ( Table1[Column] )
RETURN
    LEN ( Str ) - LEN ( SUBSTITUTE ( Str, "|", "" ) )

[MURTAZA]

@AnonymousPower Query should be best and most efficient way to do it, as mentioned by @HotChilli . However, if you would like to use DAX, try something like this:
Create a calculated column as
Count of Delimiters =

LEN ( 'Table'[Column1] ) - LEN ( SUBSTITUTE ( 'Table'[Column1], "|", "" ) )
Then you can create another column using SWITCH function as:

Result CC= SWITCH(

 TRUE(),

'Table'[Column]=0, "Level 1 (No | in string)"
.....so on.
Or you can create a measure by nesting SELECTEDVALUE like

Result Measure =

SWITCH(

 TRUE(),

 SELECTEDVALUE('Table'[Column])=0, "Level 1 (No | in string)"

.... so on

[HotChilli · Accepted solution]

I have no idea if there is a more appropriate way but this should do it. In Power Query, add a custom column:

List.Count( Text.ToList(Text.Select([Column1], "|")))

There is another function (Occurrence.All) but nobody knows how to use that!