You explain everything brilliantly, may I ask 1 stupid question? I have my column of names that everybody typed in the wrong names so I have 3 versions of a name , lets say Prima Meat Group (PTY) Ltd is the correct one, then it has been typed in , Prima meat group, Prime meat . I have a long list of these, next to your original list you have a column on the side that has a description of the the true , false, CorrEct. How must I get it there without it taking up an entire day. I my list there is at least 1 of every name that is correct. How do I go about to find it? Oh and PS. I follow your tutorial with absolute ease. I appreciate that this came along my path.🥳
@learnspreadsheets
2 ай бұрын
Thanks! Glad you find it helpful :) Microsoft have not put in a UI version yet sadly but it's coming soon. For your problem, I would make a table with two columns From and To, the load it into Power Query and use it as a Similarity Table which I showcase in the video. That should work! I'll pin this comment for others :)
@debrapretorius4565
2 ай бұрын
@learnspreadsheets thank you kindly I will try that.
@SebtorDude
Жыл бұрын
Every once in a while I stumble over a KZitem channel that deserves WAYYYY more subscribers. This is one such occasion. Very useful content!
@learnspreadsheets
Жыл бұрын
That’s a really nice comment! Thanks so much 🙃, it’s growing for sure but a bit faster would be nice ahaha
@DhruvDua88
7 ай бұрын
You sir are a certified 100% top class genius - can't find more adjectives but wow.
@learnspreadsheets
7 ай бұрын
Wow that’s so kind! Haha! Glad it helped 😃
@taylanyuksek2844
11 ай бұрын
This is pure gold!!! Thank you.
@learnspreadsheets
11 ай бұрын
Glad you enjoyed it! A secret trick :)
@JustOkko
3 ай бұрын
Hi! Very good video! However, not exactly matching my need. I have 1000s of rows already matched on a customer number level. I now want a similarity score for address in system A vs. address in system B. Do you know if there are similar functions to do something like this? I could transform the rows to columns but that would try to match every single value to all, also to those not from the same customer.
@learnspreadsheets
2 ай бұрын
Hi this method will give a similarity score, but Im not sure if that is exactly what you need.
@UE9383
9 ай бұрын
Thank you very much, I have a problem, let's say I want to match software names, some time you say MS Excel, Microsft excel and excel. now maybe this is ok with MS Excel and Excel, but not ok when Microsoft is added. Please do you have any idea how I can go around this without a transformation table?
@learnspreadsheets
9 ай бұрын
Hmm you’re asking it to know the meaning of the terms & therefore understand that Microsoft is the same as MS, chat gpt can help with some of these types of problems but you need to explicitly write out clear prompts
@leebecker8255
2 жыл бұрын
What a useful little secret!! I'm just thinking about fuzzy stuff generally. Do you think it works with nonsense strings?
@learnspreadsheets
2 жыл бұрын
Yeah it’s a nifty trick eh? It should work if the characters are close enough so should work!
@carralegend23
Жыл бұрын
Thanks for a very useful video! Is there possible to check the similarities between two exisiting columns using the same/similar function?
@learnspreadsheets
Жыл бұрын
Good question: You can use merge queries & select the same query to merge then tick the box for use fuzzy matching. Annoyingly you can’t get the similarity column though. I have a video which shows fuzzy matching
@memphisblues1980
7 ай бұрын
I would like a copy of your file please. Thank you
@learnspreadsheets
7 ай бұрын
The link to download is in the video description
@FRANKWHITE1996
Жыл бұрын
thanks for sharing
@learnspreadsheets
Жыл бұрын
Glad you like it!
@chrisaldridge7640
Жыл бұрын
Is there some way to say "This" never should match with "That". Its sort of an opposite transformation table eg I have the word Blender and it keeps matching to Bender - both words may occur and are fine as words and I don't want to increase my similarity percent to stop the match as that cuts out other results which are ok. I just don't want it ever to thinking these two words are a match.
@learnspreadsheets
Жыл бұрын
Not built in method, but a few things 1. You could use Replace Values for those specific things first then do the similar match and then Replace Values back afterwards. 2. Keep the original and changed columns, use a conditional column and put the condition original column equals certain things, return original column otherwise return changed column etc.
@AlvaroPerez-ox7ul
2 жыл бұрын
Do you need to do those orginal, mispelled charts always?
@learnspreadsheets
2 жыл бұрын
Hi you don’t need them & can delete the column from the output but it’s worth checking whether the match is as power query suggests before doing so
@AlvaroPerez-ox7ul
2 жыл бұрын
@@learnspreadsheets Thank you so much I did it!
@learnspreadsheets
2 жыл бұрын
@@AlvaroPerez-ox7ul Yay - nice!
@styx_n_stonz
Жыл бұрын
Can this be used on partial strings? e.g. look for Company Ltd or Co, Ltd or Co., Ltd at the end of a business name and change to Co. Ltd
@learnspreadsheets
Жыл бұрын
Good question. You don’t have full control over what it returns sadly, you could do that through a series of steps though using “extract text before/after delimiter” maybe
@manalehayek2291
2 жыл бұрын
Very useful thank! However I am facing an issue with the transformation table, it doesn't seem to do anything for me, any idea why?
@learnspreadsheets
2 жыл бұрын
You need to set up the transformation table in a specific way otherwise it doesn’t work the way you expect, with a from and to columns & also reference it a certain way. If you email me I can send you the workbook for this & you can see? David@xlconsulting-asia.com
@burakiswatching
Жыл бұрын
thank you!
@learnspreadsheets
Жыл бұрын
No problem!
@hussainmeghani8785
Жыл бұрын
Can you share source file on which you are working in video
@learnspreadsheets
Жыл бұрын
Files can be found on www.xlconsulting-asia.com/youtube-files.html but only recent ones, not sure about this
@hazelwong1929
2 жыл бұрын
Thank you!
@learnspreadsheets
2 жыл бұрын
Nifty trick eh?
@wayneedmondson1065
2 жыл бұрын
Useful! Thanks!!
@learnspreadsheets
2 жыл бұрын
Glad you like it, nifty little hidden trick 😃
@deure8572
2 жыл бұрын
When I type =Table... in blank query: nothing appear, do I need to install some add in? My version is 2016 profesional plus
@learnspreadsheets
2 жыл бұрын
You may not have intelli-sense. I think if you update your windows to the latest version then your power query may update too, but I’m not 100% sure a bout that, if you choose to update to office 365 or office 2021 then it should work
@deure8572
2 жыл бұрын
@@learnspreadsheets Thank you, David. I will try your suggest.
Пікірлер: 49