Multiple Key Words Search with Power Query

Подписаться 78 тыс.

Просмотров 32 тыс.

50% 1

If you wanted to search for some keyword(s) you could use Ctrl+F (and select Find All) to do this. If there was one value in the column entries, a lookup might work (like VLOOKUP) or using the merge feature in Power Query, but if you needed to lookup a word in a string of text in the cell, those lookup feature don't work that way. If you had many keywords or a list of them, this makes it even more challenging. Also if you wanted to give this to your stakeholders and provide some interactivity and hide the steps for them to do this without using VBA or show functions in your worksheet, think about using Power Query to do this.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to / doughexcel
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

Опубликовано:

5 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 66

@s18853a 2 года назад

Thank You Dough. This was so useful! Thank you for posting this.

@DougHExcel 2 года назад

You're very welcome!

@rahuljain1941 3 года назад

Thank you Doug... I was looking for the exact solution.. You are awesome !!!!

@DougHExcel 3 года назад

Happy to help!

@wayneedmondson1065 3 года назад

Hi Doug. Great Key Word search technique for Power Query. Thanks for sharing it :)) Thumbs up!!

@DougHExcel 3 года назад

Hey Wayne, thanks!

@alexandramiller7440 2 года назад

This was so useful! Thank you for posting this

@DougHExcel 2 года назад

You're so welcome!

@KhajonsakLun 2 года назад

Thank you very much from Thailand, It's very helpful and excellent example sir. I'm facing the situation like this.

@DougHExcel 2 года назад

Glad it helped!

@pasaman-_-lml6636 4 года назад

This is perfect. I really needed to do this in power query, cuz the my file sizes were getting to big. Im going to replicate but i'll use the "from folder" option for large data. Thank so much you for these power query series tutorials.

@DougHExcel 4 года назад

Glad it helped!

@matussensel8026 3 года назад

Thank you, helped a lot.

@DougHExcel 3 года назад

Glad it helped!

@renitajohnson9300 2 года назад

Thank you :) Made my work easier

@DougHExcel 2 года назад

You're welcome!

@7213261 2 года назад

Thanks!!

@DougHExcel 2 года назад

Welcome!

@DayOffFilms Год назад

Thanks so much

@DougHExcel Год назад

You’re welcome!!

@swilson1958 Год назад

Exactly what I needed for a dashboard and text with mucky cases. Thanks!!!

@DougHExcel Год назад

Glad I could help!

@colemiller3971 8 месяцев назад

Hi, I like all your videos all the videos and make you spell field 🎉 I like all your videos. What do you think about doing that in the video? What’s your next video you’re going to make about?

@krisdebaerdemaeker6699 2 года назад

Thanks Dough works very well, just one question: I need the lookup to happen non case sensitive

@DougHExcel 2 года назад

some of these might give ideas ru-vid.comsearch?query=lookup%20case

@samirkapadia3423 4 года назад

This was very helpful. Is there a way to check multiple columns and sum values

@DougHExcel 4 года назад

like this? ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ZFUcLRnlByY.html

@Seanvstherobots Месяц назад

Thanks, very useful. I have a database with job information, including keywords for each jobs and images. I tried to set up this keyword search power query which worked like a dream, however the images don't show up in the output table. Do you know if this is possible to achieve?

@ajiongyoyoajiongyoyo2614 2 года назад

Thanks Doug, I do exactly the same, it works. But still have a question: When I get a keyword like "ma", I got many results match from sentence contains it as string like "Funny matter!" other than just match it as a word, like sentence "I love my ma ma," How can I match it as a word, not as a substring of the text? Do you have any idea? Thank you very much!

@kassiowifried 4 года назад

Good Job M.Doug H .

@DougHExcel 4 года назад

Thanks kassio wifried yobonou!

@Musicomaniaa Год назад

Can we performe it for 2 columns?

@bartomiejonak5013 Год назад

Any idea how we can avoid expand custom column?

@scampydog2318 Год назад

This is Awesome 😎 so far, but stuck at conditional column, wont allow me to select "key" column keeps saying to enter txt value and shows yellow exclamation mark box. Not sure where I went wrong

@iguslife5952 3 года назад

That was cool! I just have one question: what if I wanted to do exctaly what you did, but then add another key table 2? It means that I will find the same result as yours, but the key table 2 will find more results inside the results found by key table 1, and key table 3 will find results from results found in table 2.

@DougHExcel 3 года назад

you use the output of the result as an input to another and star to "chain" the query or maybe something like this can give another idea ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-P4LOWl5qBZc.html

@gurumypassion Месяц назад

Hi Doug. Thanks for this video. It's a great time saviour. I tried this for my application where the keywords are a combination of numbers, alpha-numerals, aplhabets etc. (For example F-13.245, F-25.01 etc.) but unfortunately it is unable to match & keeps throwing an error after comparosn. Can you help me solve this?

@erdiaz39 Год назад

Thank you Dough, hey is there a way to do an exact match lookup when doing keyword search, for example trying to find the word bus it won't match in the word business.

@DougHExcel Год назад

maybe one of these will give an idea: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-_DnMuCw0IyQ.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-a46SqR0xc5Q.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-8-v_wBVWuSc.html

@erdiaz39 Год назад

@@DougHExcel Thank you!!!

@mirrrvelll5164 2 года назад

Good video. Question, can you add more text into Keywords table? like "something, something2, something3.."... separated with comma for instance. And then to look for?

@DougHExcel 2 года назад

Hi mirrr velll, thanks for the comment! If this video didn't answer than maybe these others?ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-a46SqR0xc5Q.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-8-v_wBVWuSc.html

@dipaklunawat 3 года назад

Hello Doug, I tried these steps, however, a lot of search results are coming back as null. Even though I can see the keywords in the cell that I'm trying to search in. I have around 18k records and around 45 keywords.

@DougHExcel 3 года назад

Maybe one of the other vid can give some ideas ru-vid.comsearch?query=keyword%20power%20query

@dipaklunawat 3 года назад

@@DougHExcel Thank you for your prompt reply. I tried other ways but of the odd 18K records I am only able to extract keywords out of 4k records. I have even cleaned up extra spaces and special characters. It's very frustrating. But thanks for this video. Do let me know if you have another trick for this.

@alicechuazhuling Год назад

Me too

@robinfrancis530 2 года назад

It is showing error when doing index grouping since the rows are limited to only 1000. Any other method for handling large data?

@brandongoodwin6019 2 года назад

Hi. Everything seems to be working correctly except it will only work with numbers in the keywords. How do I make it work with numbers and text in the keywords please?

@dilipreddysane 9 месяцев назад

This is Awesome. I loved it. I have one question. How to make my keyword/string case sensitive so that it can match with the string? My sting: OH- Ohio Benefits APPD License and I have keyword: AppD. The output is showing me null for this. Could you please help?

@DougHExcel 9 месяцев назад

It's probably easier to have a helper column to duplicate and make it all upper case or lower case to match with the lookup

@christopherskyap8985 3 года назад

Hi Dough H Sir, great technique, currently I am using function and invoke it to source, and if my source got more than 100K records, and keywords got also 1000 records, have not tried using this method to pick up and return the keywords, basically the sub-string that I wanted for analysis. Anyway will be =List.ContainsAny(Text.SplitAny([String], " "), Search_Words), Search_Words is the keyword tables but his one only return True and false, you got any other method that can handle huge amount of datasets thank you Sir

@DougHExcel 3 года назад

Hi Christopher Yap, thanks for the comment! Try this approach or if not some other keyword search vids at ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-_DnMuCw0IyQ.html or ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-a46SqR0xc5Q.html

@jeevank9266 3 года назад

Hey Doug, it was a great video and helped me to extract key words from a text. Is there a way to extract Dates from Text.. example is below. i have these two in one cell and need to extract earliest due date from it Test1-Payment is due on-May-05-2021 Test2-Renew on-May-15-2021

@DougHExcel 3 года назад

when the date is part of a sentence it is most likely a text string and the keyword will have to follow that format; in this case "May-05-2021"

@jeevank9266 3 года назад

@@DougHExcel yes i followed your method here and extracted both dates from sentence. now both dates are in a cell as text separated by comma and can not change it to date format. i have used delimiter to separate dates. now only thing is to get the earliest date from those two dates

@TrangHuyen-pz4od Год назад

i am fan of kakashi

@zoomingby Год назад

This was painful to get through. Jesus.

@jeremynorbury 4 года назад

You didn't really need to remerge it with itself at 9:54 - you could have just re-expanded the (ill-named) [Count] field and then removed duplicates on Index.

@DougHExcel 4 года назад

Hi Jerry Norbury, thanks for the comment! Will try that out!

@williammartinjoel2295 Год назад

For some reason, I get an error, when I try to "group by" the index column. My dataset is a lot bigger than yours. Do you have any idea why I get the error?

@user-rj7zm9tz9u 11 месяцев назад

you need to load all the data at the beggining instead of linking just by the connection

@saschamuhl8341 3 года назад

Cool Discription. After grouping the index column I get anexpression error: Expression.Error: Der Wert "null" kann nicht in den Typ "Logical" konvertiert werden. [Edit: null couldn't be to the type logical] Details: Value= Type=[Type] Could you give a hint how to deal this?

@DougHExcel 3 года назад

See if there is a change data type step and remove just to test

@amritasengupta4260 2 года назад

Was this solved? I am getting the same error

@user-rj7zm9tz9u 11 месяцев назад

you need to load all the data at the beggining instead of linking just by the connection

@bobcaruthers4427 4 месяца назад

When I tried this, it would not let me create a conditional column, It came up with the little warning triangle and asked me to enter a text value. Been here for hours, Microsoft can go do one, all there programs are designed to frustrate, irritate, and annoy. Excellent video by the way!