Search for key words with Power Query

So many wonderful ideas and solutions! Quick Access Toolbar, buffer, merge with myself, ignore.... 18 minutes packed with info! Thank you Wyn for such a fabulous video

Did not know about the comparer option, you should do a video on them all, along with Combiner.

Hi Sir. I have a similar situation, but there are items that I don't want to match and say "Others," it seems that I have to make a complete list and make the things not fit "Others." Is there a way to skip these items in the conditional step and pick them up as "Others?"

Hi.. can we search for an “exact match” like a full word .

Awesome, lots of neat little tricks missed in with the main technique

What if you wanted had multiple columns...not just ticker symbol

Hi Wyn,
I tried deriving Actual Categories with multiple matches separated by comma delimiter in sample file from your website. The query is a bit slow.
Can you see anything to speed it up?

M Code let
BufferedKeywords = Table.Buffer(Keywords),
Source = TransactionListing,
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each BufferedKeywords),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Search Word", "Category"}, {"Search Word", "Category"}),
#"Search for Keyword - Add column" = Table.AddColumn(#"Expanded Custom", "Actual Categories", each if Text.Contains([Description], [Search Word], Comparer.OrdinalIgnoreCase) then [Category] else null),
#"Removed Other Columns" = Table.SelectColumns(#"Search for Keyword - Add column",{"Index", "Actual Categories"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Index"}, {{"AllRows", each _, type table [Index=number, Actual Categories=nullable text]}}),
#"Extract Actual Categories" = Table.AddColumn(#"Grouped Rows", "Actual Categories", each [AllRows][Actual Categories]),
#"Extracted Values" = Table.TransformColumns(#"Extract Actual Categories", {"Actual Categories", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Extracted Values", {"Index"}, "Extracted Values", JoinKind.LeftOuter),
#"Expanded Extracted Values" = Table.ExpandTableColumn(#"Merged Queries", "Extracted Values", {"Actual Categories"}, {"Actual Categories"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Extracted Values", each [Actual Categories] <> null and [Actual Categories] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Actual Categories", type text}})
in
#"Changed Type"


Kind Regards

Bhavik

Hi Wyn,

Your video is helpful as I'm using it now for work.Did you make a short video showing the Excel filter function to do similar task? How about Custom Function too?

Thank you very much for sharing your knowledge.

Kind Regards

Bhavik

I like the thinking part.
:D

How can do this thing with multiple search world

Excellent video that explained key word search nicely. Can you please help me with a problem? One of my keyword is ship and Text.Contains results true even if the text in the search column contains Warship, Leadership etc. I want Text.Contains to be true only when ship is a separate word in the text. Looking forward to your help

awesome

Table.Buffer

Please bring us more use cases, very interesting function

Suuuuuperb

Thank you Wyn.. I am using this solution for my tasks since last 18 months. Missed an opportunity to thank you.

Is it possible to build this functionality in a Power BI report to have a more friendly user interface?

Learned a lot of great concepts from this. Thank you!

Superb!...learnt a lot :)

Amazing, thank you!

can you provide the link of 'Custom Function' that you referred in this video? Thanks.

Great lesson.

I am working on a dataset with 3M+ rows and some 20K+ keywords. Considering that we're directly merging & expanding all keywords against each line of transaction table, would you recommend this method?

Wow buffer table! This is what I'm looking for to fasten the refresh.

Awesome 👍. Very useful

Amazing, thanks.

This is fantastic! Thank you for putting up this tutorial!

Very good tutorial. Thanks Wyn for sharing 👍👍👍👍

Great vid. First time I've seen the Table.Buffer(Keywords) function. Very helpful.
Thanks.

Thank you. Very useful indeed.

Great! thanks for sharing!

Hi Wyn, thanks for another very helpful video. Could you please let me know how I can do something similar in Power BI? In my PowerBI workbook, I have a table (loaded from SQL server) which contains a column that I want to perform the keyword search. How can I make this column become a list/separate table itself, then add another column next to it, to define the keywords I want to use? At the moment I am using IFS statements to achieve this but my codes are lengthy and not cover 100% all words I want to search. Thank you!

Hi Wyn. Awesome tutorial! The BufferedKeywords step is a lifesaver. Thanks for demonstrating this great technique. Will bookmark this one, so I can refer to it again in the future :)) Thumbs up!!

Stunning video 😍👍🏻 after a couple hours search I found a right and easy tutorial for 1 of my problem ☺️ help me if you can with my another problem, I have a column named Account number (it’s a 12 digit number) the 5th and 6th digits belongs to supplier code, so I have to get the supplier name by matching these codes. With normal excel formula I use mid and vlookup functions to do this . Could you pls provide the link if you already done such video.
Advance thanks

Did you ever do the video using a custom function to search for key words? I am just trying to learn about custom functions.

I love the little useful nuggets you drop along the way.

Just fabulous

Hi and thanks for this excellent video that was very nice and helpful in it you covered what I have been looking for a long time!
Now I still have a question for you: How would you do the exact same in Microsoft Access SQL? I prefer using databases to use Excel. I think I have found a way to do this already but am eager to see what you would have been doing?

OMG I have been attempting to figure out how to match the two table and kept running into roadblocks. I am so glad I found this tutorial. Thanks a million.

Awesome thank you for sharing this 🔥🔥

The penny just dropped. Awesome stuff.

Thank you. If I have to learn in sequence, is there a way ?

Stunning tips, thank you so much. This one awakes multiple ideas. Kind regards from Costa Rica.

Thank you so much! Extremely helpful 😊

Very cool, and thanks for this special content

Hello, you may be able to help me. I am looking for a way to let the Power Query Editor search for specific format (here 3xA 4xNumber; example AAA8510) and then put that value into a new colum.
Reason is that that is a flightnumber that will change, but will always start three As before four numbers. Some cells does not contain a flight number, that's why I cannot just split by " - " at the end of the cell as that would split up random other stuff and I would have that stuff in the new "flight number" colum.

Is there a function that can help me?

Overwhelmingly amazing!!! So glad I stumbled upon this channel. Thank you sir!! You're awesome.

Thanks Wyn for the very useful tutorials. In the "Search Word" column, how can we apply wildcards in the way we do it in Excel formula? like we wanna determine if the description contains a "w" and then after this there is a "son", and then after these, there is a "parking" (translating to "*w*son*parking"). I tried "*w*son*parking" and "%w%son%parking" (in the query in my scenario) but no luck.

In the query that I am building there are multiple keywords that will be using wildcard logics like this. Instead of hardcoding (multiple Text.Contains joined by AND) each one by one, is there a way to do it easily?

Thank you so much for this video, this gave me a lot of new ideas and solutions

if i have 200.000 row transaction with 1200 keyword, does 'not it very slow?