Finding Mixed Keywords in a Search Query List

Hi keboon 
See if this works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lYy7CsIwFEB/5XLnDJJPsFCdOli3kCGkV3uxJiUP+vs2RAdbF7fD4XCUwo5dIjd46BdOdkSBzUhmho2Hk3lSRC0UfgmBHS2V4ebD51K6C1lyCTb528qDlPv13tb/77o1MUFjwhoV/O8A/UyWzVRPHAiuIdvHmp99jlS+cMw8DezuqPUL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Search Query" = _t, Keyword = _t]),
    #"Added All Words" = 
        Table.AddColumn(
            Source, 
            "All Words", 
            each if List.ContainsAll(Text.Split([Keyword], " "),Text.Split([Search Query], " ")) then "Keyword Found" else "Not Found",
            type text
        ),
    #"Added Words in Order" = 
        Table.AddColumn(
            #"Added All Words", 
            "Words in Order", 
            each 
                let
                    varQueryList = Text.Split([Search Query], " "),
                    varKeywordList = Text.Split([Keyword], " "),
                    varFirstWord = varQueryList{0},
                    varWordCount = List.Count(varQueryList),
                    varFirstWordPosition = List.PositionOf(varKeywordList, varFirstWord),
                    varKeywordRange = List.Range(varKeywordList, varFirstWordPosition, varWordCount),
                    varIsMatch = if List.Count(List.Difference(varQueryList, varKeywordRange)) = 0 then "Direct Keyword" else "Mixed"
                in
                if [All Words] = "Keyword Found" then varIsMatch else "No Match",
            type text
        )
in
    #"Added Words in Order"

It returns this. The red boxes are a direct match. The green are a mixed match - the words all exist but not in a specific order. The No match means none or not all words were there. The screenshot doesn't show it but the code above will properly type the new columns as text.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

Hi keboon 
was the above helpful?