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

That Buffered and Ordinal function is going to be a life saver going forward!

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

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.

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

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

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

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

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

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

This is exactly what I was looking for. Thanks so much!!! Very well explained!

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

Great video Wyn! Thanks for sharing it. Didn't know about Ignoring Ordinal Case and I already have a use-case for this ;). Thanks again!

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

Fabulous!!! I was looking for this for so long :)

Great! thanks for sharing!

Thank you so much! Extremely helpful 😊

Amazing, thank you!

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

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

I like the thinking part. :D

Great lesson.

Very cool, and thanks for this special content

Superb!...learnt a lot :)

Awesome 👍. Very useful

Thank you.👍

Amazing, thanks.

Awesome thank you for sharing this 🔥🔥

The penny just dropped. Awesome stuff.

amazing stuff

Just fabulous

Thank you. Very useful indeed.

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

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

Thanks! Buffer keeps being a mysterious function.

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?

awesome

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?

Suuuuuperb

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

Could you pls provide me the link to your other video , to do this mapping by using a custom function , as you mentioned at 03:52 , if available ? Thank you!

Great Video! How do I identify a missing category when a new purchase is made and it's not in my reference? I was hoping the category column would show an unknown category as a way to find it. any recommendation on how to output that information? thanks!

Using Table.Buffer for quadratic merge makes sense. Should we be using it for "regular" merges too to enhance performace?

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?

Excel-lent!

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, 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

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

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

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

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

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

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?

Thanks for this very instructive video. I followed each step exactly except I had different data. I found something strange though. It treated “fund” exactly the same as “refund.” Am I doing something wrong? By the way I’m a power query novice.

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

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

How can do this thing with multiple search world

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 .

Table.Buffer Please bring us more use cases, very interesting function

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