Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Implementing A Basic LIKE/Wildcard Search Function In Power Query

with 3 comments

Last week someone asked me whether it was possible to do the equivalent of a SQL LIKE filter in Power Query. Unfortunately there isn’t a function to do this in the standard library but, as always, it is possible to write some M code to do this. Here’s what I came up while I was waiting around at the stables during my daughter’s horse-riding lesson. At the moment it only supports the % wildcard character; also I can’t guarantee that it’s the most efficient implementation or indeed 100% bug-free, but it seems to work fine as far as I can see…

let

    Like = (Phrase as text, Pattern as text) => 

let

    //Split pattern up into a list using % as a delimiter

    PatternList = Text.Split(Pattern, "%"),

    //if the first character in the pattern is %

    //then the first item in the list is an empty string

    StartsWithWc = (List.First(PatternList)=""),

    //if the last character in the pattern is %

    //then the last item in the list is an empty string

    EndsWithWc = (List.Last(PatternList)=""),

    //if the first character is not %

    //then we have to match the first string in the pattern

    //with the opening characters of the phrase

    StartsTest = if (StartsWithWc=false) 

       then Text.StartsWith(Phrase, List.First(PatternList)) 

       else true,

    //if the last item is not %

    //then we have to match the final string in the pattern

    //with the final characters of the phrase

    EndsText = if (EndsWithWc=false) 

       then Text.EndsWith(Phrase, List.Last(PatternList)) 

       else true,

    //now we also need to check that each string in the pattern appears 

    //in the correct order in the phrase

    //and to do this we need to declare a function PhraseFind

    PhraseFind = (Phrase as text, SearchString as list) =>

    let

     //does the first string in the pattern appear in the phrase?

     StringPos = Text.PositionOf(Phrase, SearchString{0}, Occurrence.First),

     PhraseFindOutput = 

                 if

                 //if string not find then return false 

                 (StringPos=-1) 

                 then false 

                 else if

                 //we have found the string in the pattern, and

                 //if this is the last string in the pattern, return true

                 List.Count(SearchString)=1

                 then true

                 else

                 //if it isn't the last string in the pattern

                 //test the next string in the pattern by removing

                 //the first string from the pattern list

                 //and all text up to and including the string we have found in the phrase

                 (true and

                 @PhraseFind(

                 Text.RemoveRange(Phrase, 0, StringPos + Text.Length(SearchString{0})),

                 List.RemoveRange(SearchString, 0, 1)))

     in

      PhraseFindOutput,

    //return true if we have passed all tests    

    Output = StartsTest and EndsText and PhraseFind(Phrase, PatternList) 

in

    Output

in

    Like

 

Using the following test data:

image

I can run the following query:

let

    Source = Excel.CurrentWorkbook(){[Name="Phrases"]}[Content],

    ChangedType = Table.TransformColumnTypes(Source,{{"Phrases", type text}}),

    InsertedCustom = Table.AddColumn(ChangedType, "Test", each Like([Phrases],"%cat%sat%mat%"))

in

    InsertedCustom

 

And get this output:

image

You can download the sample workbook here.

I know the Power Query team have been asked for this several times already, but it would be really useful if we could package up functions like this and make it easy to share them publicly with other Power Query users…

Written by Chris Webb

May 27, 2014 at 2:19 pm

Posted in Power BI, Power Query

3 Responses

Subscribe to comments with RSS.

  1. […] Implementing A Basic LIKE/Wildcard Search Function In Power Query (Chris Webb) […]

  2. […] last week’s post I showed how to create a simple LIKE function in Power Query which could be used in a calculated […]

  3. […] last week’s post I showed how to create a simple LIKE function in Power Query which could be used in a calculated […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,240 other followers

%d bloggers like this: