Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ) |
Solved! Go to 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!
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, "|", "" ) )
@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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |