cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kapillehar
Helper I
Helper I

Filter extraction in Query editor

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

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User III
Super User III

Hi @kapillehar 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

5 REPLIES 5
Payeras_BI
Super User I
Super User I

Hi @kapillehar ,

I am with you and would also go for @PhilipTreacy 's solution.

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
smpa01
Memorable Member
Memorable Member

@kapilleharplease 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.

Capture.PNG

 

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"

New Animated Dashboard: Sales Calendar


PhilipTreacy
Super User III
Super User III

Hi @kapillehar 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Payeras_BI
Super User I
Super User I

Hi @kapillehar ,

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. 

Payeras_BI_0-1612009103905.png

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_1-1612009235930.pngPayeras_BI_2-1612009262145.png

Payeras_BI_4-1612009559233.png

Payeras_BI_3-1612009281023.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

@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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.