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
Resident Rockstar
Resident Rockstar

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

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors