Search for key words with Power Query

[@GrainneDuggan_Excel]

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

[@AccessAnalytic]

Thanks for the kind feedback

[@ravchana2393]

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

[@AccessAnalytic]

Nice one

[@leftindependent]

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

[@AccessAnalytic]

Glad to help

[@leftindependent]

@@AccessAnalytic just one small question - what if I have the two keywords that relates to the same row? For example, I have keyword payable and keyword parent company. The first one relates to payables category, the second one relates to intercompany category. The problem is that I have the row with texy parent company payable. It should be assigned to intercompany, but is assigned to payables, because it takes the category value from the first encountered row. I would prefer to get a "combined category" like payables intercompany os so. It would be much easier to spot such cases. Do you how to do that?

[@AccessAnalytic]

@leftindependent not sure sorry

[@leftindependent]

@@AccessAnalytic I worked this out 😁

[@ShekaneSeiro]

This is the best video I've seen on this topic full of helpful tips - thank you so much!

[@AccessAnalytic]

You’re welcome.

[@SMITDEDHIA96]

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

[@AccessAnalytic]

Thanks Smit

[@davidferrick]

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

[@AccessAnalytic]

Thanks David, comments like this encourage me to do more videos :)

[@SMH2007]

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.

[@AccessAnalytic]

Excellent Salina. Thanks for letting me know, it keeps me motivated to create more videos in future.

[@Sumanth1601]

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

[@AccessAnalytic]

Fantastic, thanks for taking the time to come back and let me know

[@wayneedmondson1065]

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

[@AccessAnalytic]

Cheers Wayne

[@cezarconstantino7962]

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

[@AccessAnalytic]

Great, thanks for letting me know it helped

[@joseantoniolozanomartinez2854]

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

[@AccessAnalytic]

Glad it helped. Thank you

[@virterra]

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

[@AccessAnalytic]

Thanks, I appreciate you taking time to leave a comment

[@AdrianLospa]

Very nice, I was looking for a way to assign categories based on keywords in Power Query! Your video really helped! Much appreciated!

[@AccessAnalytic]

You’re welcome. I appreciate you taking the time to let me know you found it useful

[@sajilprkkv]

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!

[@AccessAnalytic]

Hi Sajil, I don’t think I did get around to doing that specific example. I have another video explaining custom functions here kzbin.info/www/bejne/mYGxY2eEgJiSbJo

[@anthonyfernandez894]

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

[@AccessAnalytic]

Thanks for letting me know Anthony 😀

[@ShahmeranGilani]

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

[@AccessAnalytic]

Glad to help Shahmeran

[@yankeeking213]

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

[@AccessAnalytic]

Glad you liked it Chris, make sure you check out the Power Query Challenges we're now doing accessanalytic.com.au/power-on-with-this-power-query-challenge/ . I post videos of the solutions here

[@cristian.angyal]

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!

[@AccessAnalytic]

You're welcome Cristian. I didn't know about it either until I looked it up to do this exercise ! 😂

[@kebincui]

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

[@AccessAnalytic]

Thanks for letting me know you liked it Kebin

[@armanazote6230]

I like the thinking part. :D

[@TheTeatoe]

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!

[@AccessAnalytic]

I think the logic would be that if a line has no matching keywords it would get called out. At 7:06 where you have index and categories you could duplicate the query at that point and then add a column if null then 0, groupby, sum, Filter for 0 ( since those that sum to 0 have no matches )

[@cedjulemckeever]

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

[@AccessAnalytic]

Hi, this video includes custom functions kzbin.info/www/bejne/mYGxY2eEgJiSbJo

[@williamarthur4801]

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

[@AccessAnalytic]

Thanks for the suggestion

[@peltiertech1879]

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

[@AccessAnalytic]

Cheers Jon

[@dullo99]

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?

[@AccessAnalytic]

Thanks for the kind comment Vidar. Power Query does not exist for Access and I’m not a SQL person sorry.

[@singhalmonica]

Great lesson.

[@AccessAnalytic]

Thanks Monica

[@z.719]

Awesome 👍. Very useful

[@AccessAnalytic]

Thanks

[@thealchemist6133]

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

[@AccessAnalytic]

Sorry I never did get round to doing a specific one related to this, although I did show how to build a customer function here kzbin.info/www/bejne/mYGxY2eEgJiSbJo

[@adriannacook313]

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!

[@AccessAnalytic]

Hi Adrianna, you need to export the list to Excel and then add the keywords there. Then either pull that Excel file in or paste into Power BI using the Enter Data option

[@adriannacook313]

@@AccessAnalytic thanks so much Wyn. I’ll give this a go. I have been wondering, whether this process can be automated, so that the excel file will update when my data source has an update. Do you happen to know a solution for this? In addition, I have to group by data based on key words in power bi fairly often, could you please share with me some efficient approaches for this kind of task? I usually use if statements but they become so lengthy. For example, I would write something like this: If [main column to search from] = A and text.contains(“green apple”, [main column to search from], comparer.ignorecase) or text.contains (“red Apple”, ....) or not text.contains... Then Else if I find that I need to repeat my conditions over and over again to make them run correctly, otherwise the OR condition will take over and cause incorrect results. Thanks Wyn.

[@AccessAnalytic]

In terms of automation I'm not sure I get what you mean. If you are searching for keywords then you just have to manually maintain that keyword table. For the second part, I may be missing something in your question, as I the purpose of the video was to address this scenario.

[@joannazibbell4149]

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

[@AccessAnalytic]

I don't quite understand the question Zoanna. Do you want end users to do a search on a Power BI report? e.g. like using a searchable slicer?

[@mrstevenbne]

Great! thanks for sharing!

[@alexj8117]

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

[@AccessAnalytic]

Probably not Alex. I'd recommend this article to start learning about speed optimisation www.thebiccountant.com/speedperformance-aspects/

[@vijaykrishnan4151]

Superb!...learnt a lot :)

[@SMITDEDHIA96]

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?

[@AccessAnalytic]

No it’s unlikely to work with that much data. That scale of data is outside of my knowledge sorry

[@avishiinternational5338]

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

[@AccessAnalytic]

Maybe put a space after the word ship?

[@Bhavik_Khatri]

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

[@AccessAnalytic]

Hi, thanks Bhavik. I don’t have those videos

[@Bhavik_Khatri]

@@AccessAnalytic Looking fwd for them. I'm waiting for Nov to get a printed version of your new book. Thank you very much for your help.

[@AccessAnalytic]

You’re welcome

[@hariyantowidjaja6167]

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

[@AccessAnalytic]

Quite possibly, especially if running on 32 Bit Excel

[@robertlevak]

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

[@AccessAnalytic]

You’re welcome Robert

[@luihoratio621]

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?

[@africanbea]

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?"

[@AccessAnalytic]

Can you replace null's with "Other" ?

[@africanbea]

@@AccessAnalytic First of all, I would like to thank you for your prompt response, and I was very much delighted to hear back from you. I'm getting an error "We cannot convert the value null to type logical" when I try to expand the column in the last step after merge

[@africanbea]

@@AccessAnalytic I came across this video, and I was able to solve the issue. kzbin.info/www/bejne/n5bUhIOHgcSol8U

[@AccessAnalytic]

@@africanbea Glad you solved it

[@mohammedmusthafa2849]

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

[@AccessAnalytic]

Sounds like just a normal Merge in that case I think

[@mohammedmusthafa2849]

Hi.. thanks for your reply.. Eg. I want to search for "Lin" among a group of words. It should not return words like linked, Lincoln or Linda. I want just and only Lin. Thanks

[@AccessAnalytic]

Not sure how to do that sorry

[@mohammedmusthafa2849]

Hi..I managed to find a solution by combining your method and a list function I learned from another online commentator

[@AccessAnalytic]

Good stuff!

[@abdullahalnaiem3501]

How can do this thing with multiple search world

[@AccessAnalytic]

I’ve not tried that sorry.

[@mnowako]

Amazing, thank you!

[@AccessAnalytic]

Thanks for all your kind comments Mariusz

[@Rewe4life]

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?

[@AccessAnalytic]

High Max Power, there is a Split Column option on the Home Menu allowing you to split by Number of Characters, or by Non Digit to Digit. This sounds like it would do the trick. The other option is to Right Click the column and choose the Column From Examples option and type in what you want to extract and press Enter... you may need to type in 2 or 3 examples for it to pick up the pattern. Hope that helps

[@Rewe4life]

@@AccessAnalytic awesome. It worked with the exmaples. Now I have another task that I try to achieve. It is very difficult to only describe by words. Is there a way that we can talk over discord or so so that I can show you some screenshots etc?

[@AccessAnalytic]

Hi @@Rewe4life , unfortunately that's not something I do, I'd suggest posting as much info as you can to the Tech Community and get some advice there techcommunity.microsoft.com/t5/excel/ct-p/Excel_Cat

[@granand]

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

[@AccessAnalytic]

Not sure what you mean sorry?

[@granand]

@@AccessAnalytic Sorry, I mean videos from the beginning so that I know my progress.

[@AccessAnalytic]

Maybe the Power Query playlist will help ? kzbin.info/aero/PLlHDyf8d156UFChHzgQIO2cdaNqOS8KX3

[@granand]

@@AccessAnalytic Thank you, I will follow

[@naveenviegas3498]

Just fabulous

[@AccessAnalytic]

Thank you kindly Naveen

[@Acheiropoietos]

The penny just dropped. Awesome stuff.

[@mattahmad4387]

amazing stuff

[@AccessAnalytic]

Cheers

[@jon99long]

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.

[@AccessAnalytic]

Hi, thanks for the question. It may be that it is doing a partial match. It’s been quite a long time since I did this technique. Check out the channels Goodly, Bi Gorilla, and MyOnlineTrainingHub to see if they’ve done an exact match technique. You could also post your requirement to www.reddit.com/r/PowerBI/ or www.reddit.com/r/excel/ Or community.powerbi.com/t5/Power-Query/bd-p/power-bi-services

[@RDiazJimenez]

Thanks! Buffer keeps being a mysterious function.

[@AccessAnalytic]

It is to most of us RIcardo!

[@PValili]

Thank you. Very useful indeed.

[@AccessAnalytic]

Glad you found it useful, thanks for letting me know

[@PValili]

@@AccessAnalytic The Table.Buffer() is such a cool feature.

[@AccessAnalytic]

Yep but it needs to be used carefully, can slow down queries too in some cases. Check out Chris Webb and Imke Feldmann’s blogs for more info

[@claireann4017]

Thank you so much! Extremely helpful 😊

[@AccessAnalytic]

You’re welcome Sherilana

[@954giggles]

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

[@AccessAnalytic]

Not sure what you mean by ticker symbol sorry. Is there a time reference on the video that you can point me to?

[@FaisalBokhowah]

Very cool, and thanks for this special content

[@AccessAnalytic]

Thanks Faisal, I appreciate you leaving a kind comment

[@gopichand5717]

Awesome thank you for sharing this 🔥🔥

[@AccessAnalytic]

You’re welcome Gopi

[@miazhou8902]

Amazing, thanks.

[@AccessAnalytic]

Thank you for letting me know you liked it

[@sajilprkkv]

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

[@AccessAnalytic]

Thanks Sajil. The Power Query equivalent of mid is under Add Columns - Extract - Range Then the equivalent of VLOOKUP Is merge ( in the Home tab ). It’s worth considering avoiding merge and loading you data and the supplier code table into the data model and connecting them with a relationship. Check out my 10 million rows of data video if you’re not sure what I mean there.

[@sajilprkkv]

@@AccessAnalytic thank you for the reply ...sure I will have a look and will update ☺️👍🏻

[@GoodmanMalaya9291]

awesome

[@AccessAnalytic]

Thanks

[@reanalytics1863]

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

[@AccessAnalytic]

Cheers, I don't have too many use cases, it's a rarely used one

[@Luciano_mp]

Thank you.👍

[@brij26579]

Suuuuuperb

[@AccessAnalytic]

Thanks

[@DougHExcel]

Excel-lent!

[@Bhavik_Khatri]

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

[@AccessAnalytic]

How many rows is your data source? How many words are you searching for? This does run row by row so is a slow operation

[@Bhavik_Khatri]

​​Hi Wyn, I have 9,984 rows of data with 6 Keywords as per your sample file. Is there a smart or better way? I'm still new to PQ and want to adopt the best programming approaches. Kind Regards, Bhavik

[@AccessAnalytic]

Not that I know of

[@Bhavik_Khatri]

@@AccessAnalytic Thank you for looking into my query.