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.
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.
maybe one of these will give an idea: th-cam.com/video/_DnMuCw0IyQ/w-d-xo.html th-cam.com/video/a46SqR0xc5Q/w-d-xo.html th-cam.com/video/8-v_wBVWuSc/w-d-xo.html
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?
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 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.
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?
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?
Hi mirrr velll, thanks for the comment! If this video didn't answer than maybe these others?th-cam.com/video/a46SqR0xc5Q/w-d-xo.html th-cam.com/video/8-v_wBVWuSc/w-d-xo.html
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!
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
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?
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?
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?
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.
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?
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.
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 th-cam.com/video/P4LOWl5qBZc/w-d-xo.html
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?
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 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
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
Hi Christopher Yap, thanks for the comment! Try this approach or if not some other keyword search vids at th-cam.com/video/_DnMuCw0IyQ/w-d-xo.html or th-cam.com/video/a46SqR0xc5Q/w-d-xo.html
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!
Thank You Dough. This was so useful! Thank you for posting this.
You're very welcome!
Thank you very much from Thailand, It's very helpful and excellent example sir. I'm facing the situation like this.
Glad it helped!
This was so useful! Thank you for posting this
You're so welcome!
Thank you Doug... I was looking for the exact solution.. You are awesome !!!!
Happy to help!
Hi Doug. Great Key Word search technique for Power Query. Thanks for sharing it :)) Thumbs up!!
Hey Wayne, thanks!
Thank you :) Made my work easier
You're welcome!
Thank you, helped a lot.
Glad it helped!
Exactly what I needed for a dashboard and text with mucky cases. Thanks!!!
Glad I could help!
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.
Glad it helped!
Thanks so much
You’re welcome!!
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.
maybe one of these will give an idea:
th-cam.com/video/_DnMuCw0IyQ/w-d-xo.html
th-cam.com/video/a46SqR0xc5Q/w-d-xo.html
th-cam.com/video/8-v_wBVWuSc/w-d-xo.html
@@DougHExcel Thank you!!!
Thanks!!
Welcome!
Thanks Dough works very well, just one question: I need the lookup to happen non case sensitive
some of these might give ideas th-cam.com/users/DougHExcelsearch?query=lookup%20case
This was very helpful. Is there a way to check multiple columns and sum values
like this? th-cam.com/video/ZFUcLRnlByY/w-d-xo.html
Can we performe it for 2 columns?
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?
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.
Maybe one of the other vid can give some ideas
th-cam.com/users/DougHExcelsearch?query=keyword%20power%20query
@@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.
Me too
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?
This was painful to get through. Jesus.
Any idea how we can avoid expand custom column?
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?
Hi mirrr velll, thanks for the comment! If this video didn't answer than maybe these others?th-cam.com/video/a46SqR0xc5Q/w-d-xo.html
th-cam.com/video/8-v_wBVWuSc/w-d-xo.html
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!
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
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?
Good Job M.Doug H .
Thanks kassio wifried yobonou!
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?
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
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?
you need to load all the data at the beggining instead of linking just by the connection
It is showing error when doing index grouping since the rows are limited to only 1000. Any other method for handling large data?
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.
Hi Jerry Norbury, thanks for the comment! Will try that out!
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?
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.
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 th-cam.com/video/P4LOWl5qBZc/w-d-xo.html
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?
See if there is a change data type step and remove just to test
Was this solved? I am getting the same error
you need to load all the data at the beggining instead of linking just by the connection
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
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"
@@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
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
Hi Christopher Yap, thanks for the comment! Try this approach or if not some other keyword search vids at th-cam.com/video/_DnMuCw0IyQ/w-d-xo.html or th-cam.com/video/a46SqR0xc5Q/w-d-xo.html
i am fan of kakashi
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!