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.
Hi,
I need to highlight all rows that have the letters BE followed by 2 numbers in a long text field. Is this possible?
Thanks in advance
Solved! Go to Solution.
Ohh,
Try this:
List.Transform(
List.RemoveItems(
List.Transform(
List.RemoveFirstN(
Text.Split([Column1] , "BE"),
1),
each try
Number.FromText(Text.End(Text.Start("BE" & _,4),2)) otherwise null
), {null}
), each "BE" & Number.ToText(_))
@mdex , a new measure
If(left(max(Table[Column]),2) = "BE" && mid( max(Table[Column]),3,1) >= '0' && mid( max(Table[Column]),3,1) <= '9' && mid( max(Table[Column]),4,1) >= '0' && mid( max(Table[Column]),4,1) <= '9', "Green", "red")
And use this conditional formatting using the filed value How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4
Hi,
Thanks for helping. This is very close but seems to be expecting "BE" to be at the start of the field, it could actually be anywhere.
E.g. "TEST REVIEW BE22 Incident"
Hi @mdex,
I would proberbly use the Split on delimiter or extract on delimiter functions in Power Query to identify if there is a "BE" present in the string column. Then with your new column from the split you can then use a LEFT( Table[Column] , 2) to identify the two numbers.
Br,
Johannes
If BE appears in a word before the BE22 then that would cause problems?
"Been to visit BE22"
Hi!
You are very correct. I've created a new power query column that you can try!
List.Transform(
List.RemoveItems(
List.Transform(
Text.Split([Column1] , "BE"),
each try
Number.FromText(Text.End(Text.Start("BE" & _,4),2)) otherwise null
), {null}
), each "BE" & Number.ToText(_))
It might be a good idea to uppercase the column first.
I've also attached a pbix to this message.
/ J
Really appreciate the assistance tex and sorry for coming back so late.
This method doesn't quite work either. Using your example pbix it identifies some incorrect BE## combinations.
Ohh,
Try this:
List.Transform(
List.RemoveItems(
List.Transform(
List.RemoveFirstN(
Text.Split([Column1] , "BE"),
1),
each try
Number.FromText(Text.End(Text.Start("BE" & _,4),2)) otherwise null
), {null}
), each "BE" & Number.ToText(_))
Thanks so much tex, Now I'll research those functions and find out what they actually do!
No worries! Let me know if there is anything you're wondering about 🙂
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |