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 have a column called title that has values like:
1 202 User story
2 2020-2021
I just want to filter titles that have 202.
When I use the contain filter, it gives me both the rows since 2020 also has 202. How can i exclude such instances?
I want to do this in M code
also note that the 202 substring can be anywhere inside the title
Solved! Go to Solution.
Hi @Anonymous
Try filtering for "202 " , that is a total of 4 characters 202 and a space.
Or filter to keep 202 and then filter again to remove 20-
Regards
Phil
Proud to be a Super User!
Hi @Anonymous ,
I am with you and would also go for @PhilipTreacy 's solution.
@Anonymousplease invoke the following custom function on the column containing 202 string
//function qx
let
fx=(a)=>
Web.Page(
"<script>
x = '"&a&"';
y=x.match(/202/gm)
document.write(y);
</script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text]
in
fx
. This function has the capacity to extract 202 from any coordinate within a string
e.g.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwUorViVZKBAEYJykJyKqsrARz9EEgBg6sQQBFtTGIjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each qx([Column1]))
in
#"Added Custom"
Hi @Anonymous
Try filtering for "202 " , that is a total of 4 characters 202 and a space.
Or filter to keep 202 and then filter again to remove 20-
Regards
Phil
Proud to be a Super User!
Hi @Anonymous ,
A possible code-free solution.
In the example below you need to keep the rows having the substring at the beginning, in the middle or at the end.
Duplicate the column, split it into rows by each occurrence of the space delimiter and keep only those that are exact matches of your substring.
@Payeras_BI loved your solution and it is more robust. However I guess in terms of ease, @PhilipTreacy's solution was better. I wonder how I coudn't figure this out on my own, although handing such cases using a space doesnt make sense. I was expecting regex type of solution but a problem solved is a problem solved.
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |