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
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 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?
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!!
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!
Hi Sajil, I don’t think I did get around to doing that specific example. I have another video explaining custom functions here ua-cam.com/video/cPN24NK3_68/v-deo.html
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
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!
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 )
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?
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 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
@@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.
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.
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?
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
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 ua-cam.com/video/cPN24NK3_68/v-deo.html
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?
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
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
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.
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?
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
@@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?
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
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 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
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
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.
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
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
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
Thanks for the kind feedback
That Buffered and Ordinal function is going to be a life saver going forward!
Nice one
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.
Excellent Salina. Thanks for letting me know, it keeps me motivated to create more videos in future.
Thank you Wyn.. I am using this solution for my tasks since last 18 months. Missed an opportunity to thank you.
Fantastic, thanks for taking the time to come back and let me know
Wow buffer table! This is what I'm looking for to fasten the refresh.
Great, thanks for letting me know it helped
Overwhelmingly amazing!!! So glad I stumbled upon this channel. Thank you sir!! You're awesome.
Thanks Smit
This is the best video I've seen on this topic full of helpful tips - thank you so much!
You’re welcome.
Fabulous!!! I was looking for this for so long :)
Glad to help
@@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?
@leftindependent not sure sorry
@@AccessAnalytic I worked this out 😁
I love the little useful nuggets you drop along the way.
Thanks David, comments like this encourage me to do more videos :)
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!!
Cheers Wayne
Very nice, I was looking for a way to assign categories based on keywords in Power Query! Your video really helped! Much appreciated!
You’re welcome. I appreciate you taking the time to let me know you found it useful
This is exactly what I was looking for. Thanks so much!!! Very well explained!
Glad it helped. Thank you
Great vid. First time I've seen the Table.Buffer(Keywords) function. Very helpful.
Thanks.
Thanks, I appreciate you taking time to leave a comment
Stunning tips, thank you so much. This one awakes multiple ideas. Kind regards from Costa Rica.
Thanks for letting me know Anthony 😀
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!
You're welcome Cristian. I didn't know about it either until I looked it up to do this exercise ! 😂
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!
Hi Sajil, I don’t think I did get around to doing that specific example. I have another video explaining custom functions here ua-cam.com/video/cPN24NK3_68/v-deo.html
This is fantastic! Thank you for putting up this tutorial!
Glad to help Shahmeran
I like the thinking part.
:D
Learned a lot of great concepts from this. Thank you!
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
Very good tutorial. Thanks Wyn for sharing 👍👍👍👍
Thanks for letting me know you liked it Kebin
Awesome, lots of neat little tricks missed in with the main technique
Cheers Jon
Did not know about the comparer option, you should do a video on them all, along with Combiner.
Thanks for the suggestion
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!
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 )
Great! thanks for sharing!
Superb!...learnt a lot :)
Thank you so much for this video, this gave me a lot of new ideas and solutions
You’re welcome Robert
Great lesson.
Thanks Monica
Using Table.Buffer for quadratic merge makes sense. Should we be using it for "regular" merges too to enhance performace?
Probably not Alex. I'd recommend this article to start learning about speed optimisation www.thebiccountant.com/speedperformance-aspects/
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?
Thanks for the kind comment Vidar. Power Query does not exist for Access and I’m not a SQL person sorry.
Awesome 👍. Very useful
Thanks
Thanks! Buffer keeps being a mysterious function.
It is to most of us RIcardo!
Did you ever do the video using a custom function to search for key words? I am just trying to learn about custom functions.
Hi, this video includes custom functions ua-cam.com/video/cPN24NK3_68/v-deo.html
Just fabulous
Thank you kindly Naveen
The penny just dropped. Awesome stuff.
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 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
@@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.
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.
amazing stuff
Cheers
Amazing, thank you!
Thanks for all your kind comments Mariusz
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?
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
Hi, thanks Bhavik. I don’t have those videos
@@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.
You’re welcome
can you provide the link of 'Custom Function' that you referred in this video? Thanks.
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 ua-cam.com/video/cPN24NK3_68/v-deo.html
Very cool, and thanks for this special content
Thanks Faisal, I appreciate you leaving a kind comment
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?
No it’s unlikely to work with that much data. That scale of data is outside of my knowledge sorry
Awesome thank you for sharing this 🔥🔥
You’re welcome Gopi
Thank you. Very useful indeed.
Glad you found it useful, thanks for letting me know
@@AccessAnalytic The Table.Buffer() is such a cool feature.
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
Amazing, thanks.
Thank you for letting me know you liked it
Thank you so much! Extremely helpful 😊
You’re welcome Sherilana
Thank you.👍
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
Maybe put a space after the word ship?
Is it possible to build this functionality in a Power BI report to have a more friendly user interface?
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?
awesome
Thanks
Thank you. If I have to learn in sequence, is there a way ?
Not sure what you mean sorry?
@@AccessAnalytic Sorry, I mean videos from the beginning so that I know my progress.
Maybe the Power Query playlist will help ? ua-cam.com/play/PLlHDyf8d156UFChHzgQIO2cdaNqOS8KX3.html
@@AccessAnalytic Thank you, I will follow
Suuuuuperb
Thanks
if i have 200.000 row transaction with 1200 keyword, does 'not it very slow?
Quite possibly, especially if running on 32 Bit Excel
What if you wanted had multiple columns...not just ticker symbol
Not sure what you mean by ticker symbol sorry. Is there a time reference on the video that you can point me to?
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
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.
@@AccessAnalytic thank you for the reply ...sure I will have a look and will update ☺️👍🏻
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?
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
@@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?
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
How can do this thing with multiple search world
I’ve not tried that sorry.
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?"
Can you replace null's with "Other" ?
@@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
@@AccessAnalytic I came across this video, and I was able to solve the issue. ua-cam.com/video/ieqSPQL_uac/v-deo.html
@@africanbea Glad you solved it
Hi.. can we search for an “exact match” like a full word .
Sounds like just a normal Merge in that case I think
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
Not sure how to do that sorry
Hi..I managed to find a solution by combining your method and a list function I learned from another online commentator
Good stuff!
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.
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
Table.Buffer
Please bring us more use cases, very interesting function
Cheers, I don't have too many use cases, it's a rarely used one
Excel-lent!
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
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
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
Not that I know of
@@AccessAnalytic Thank you for looking into my query.