cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
keboon
Microsoft
Microsoft

Finding Mixed Keywords in a Search Query List

Hi All:

I am trying to figure out a formula for the Results column that when I look for keywords in the Search Query List based on the Keyword List I will find the keywords either Direct (Nintendo Switch) or Indirect / Mixed (Switch Games).  If anybody can help, I appreciate it.

 

Thank You!

 

Screenshot 2022-06-15 155652.png

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

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.

edhans_0-1655332016920.png

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

edhans
Super User
Super User

Hi @keboon 
was the above helpful?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
edhans
Super User
Super User

Hi @keboon 
was the above helpful?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

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.

edhans_0-1655332016920.png

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors