Nice one covering the fuzzy match. I didn't know there is a transformation table option! Thanks!
@MyOnlineTrainingHub3 жыл бұрын
Glad you discovered something new 😊
@markbainbridge32403 жыл бұрын
Fuzzy logic plus Fuzzy Matching is perfectly compatible with my Fuzzy brain. More seriously as ever a very well and clearly explained tip. I definitely want to become a power query super user.
@MyOnlineTrainingHub3 жыл бұрын
Great to hear, Mark 😄
@cherianiype3 жыл бұрын
That is just awesome! Super fantastic! Incredible stuff! Very many thanks Mynda and Phil and team! Very much appreciate this. Where is the LOVE ICON?!?
@MyOnlineTrainingHub3 жыл бұрын
Glad you enjoyed it, Cherian!
@anaismartin73843 жыл бұрын
Really interesting, but Phil is a little bit fast when he shows things. It would be cool if he slowed down just a tiny little bit. Plus, his diction is less clear to understand than Mynda's for me (french).
@MyOnlineTrainingHub3 жыл бұрын
Thanks for the feedback, Anais!
@earningthomas46408 ай бұрын
This idea of fuzzy merge seems very promising. It reminds me of the xlookup function in a way. I think they are both similar in the sense of being able to shift parameters from 100% to less than 100% matches. However, i think the power query example is a bit more reliable because you could litterally dial in the confidence level you want to achieve. I like that aspect to it. I'm sure i could find a million ways to use this.
@MyOnlineTrainingHub7 ай бұрын
Great points about Power Query's confidence level 👍
@wayneedmondson10653 жыл бұрын
Hi Mynda and Phil. Thanks for demonstrating fuzzy matching in Power Query. Although it is an enticing feature, I find it difficult to trust it except under the most simple circumstances. Even though it is more work, I prefer a transformation table to be the primary method against any data that really matters. That said, your demo of combining fuzzy match and a transformation table is a good use case, making the transformation table shorter for only those items that PQ can't resolve. Excellent! Thanks for sharing :)) Thumbs up!!
@MyOnlineTrainingHub3 жыл бұрын
Cheers, Wayne! Good points.
@geeves213122 жыл бұрын
How can a transformation table take the place of this? Assuming I understand what you mean by a transformation table: My use of them is a set of defined-by-me values that are used for transformation... but therefore I need to be explicit that a person may have entered a period instead of a space, or worse still, defined every possible combination. Does my table for "Sydney" need to say: Sydney, Sdney, Syney, Sydey, Sydny, Sydne, Dney, Dsney, etc? That isn't practical?
@wayneedmondson10652 жыл бұрын
@@geeves21312 Hi GB. Yes, a transformation table needs to be explicit, so if you have many possible combinations, then they would all need to be in the table. How to approach it depends on the data and the use. If a mismatch would be costly or otherwise serious, then you have to take whatever steps necessary to insure it will be correct. If otherwise, then you can be more lax, such as using fuzzy match. Every circumstance is different. So, it depends on your use case. Hope this helps. Good luck!!
@teoxengineer3 жыл бұрын
Woooww this is so a secret feature of PQ. 😵 Thanks a lot Mynda!
@MyOnlineTrainingHub3 жыл бұрын
Glad you like it, Emre!
@josecarlosconejo57243 жыл бұрын
Very useful tip. Thanks to you both for sharing and being so didactic (an ability that I highly appreciate)
@MyOnlineTrainingHub3 жыл бұрын
Thank you :-) Our pleasure, Jose!
@ahmadeid23 жыл бұрын
From Egypt, thank you Mynda ,we love you 💓
@MyOnlineTrainingHub3 жыл бұрын
You are so welcome, Ahmad!
@Luciano_mp3 жыл бұрын
Great. I liked. Thanks Mynda!
@MyOnlineTrainingHub3 жыл бұрын
Cheers, Luciano! 😊
@clickbisi70163 жыл бұрын
This is GOLD! 🙌🏾
@MyOnlineTrainingHub3 жыл бұрын
Glad you like it :-)
@larato55443 жыл бұрын
Thank you Mynda and Phil. That will be really useful.
@MyOnlineTrainingHub3 жыл бұрын
Great to hear 😊
@ToddJ32611 ай бұрын
This is helpful, but can you explain how he got the data for the Transform table?
@MyOnlineTrainingHub11 ай бұрын
The transform table was created in Excel and loaded via Get Data > From Table/Range connector, but you can import it from any source supported by Power Query.
@SR711125 ай бұрын
very good exponation!
@MyOnlineTrainingHub5 ай бұрын
Glad it was helpful!
@KuldeepSingh-nq1vi2 жыл бұрын
Hi Munda, it's very useful technique.
@MyOnlineTrainingHub2 жыл бұрын
Glad you liked it!
@munkh-erdenechimiddorj90257 ай бұрын
Thank you for the video. Can you tell me how you create a new table with from and to columns?
@MyOnlineTrainingHub7 ай бұрын
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@darrylmorgan3 жыл бұрын
Hi Mynda/Phil Great Tips For Using Fuzzy Match...Thank You :)
@MyOnlineTrainingHub3 жыл бұрын
Cheers, Darryl!
@shoaibrehman99883 жыл бұрын
Actually i was doing today learned more now about transform table.
@MyOnlineTrainingHub3 жыл бұрын
Great to hear 😊
@YouExcelTutorials3 жыл бұрын
Cool stuff. Thanks for sharing Mynda & Phil.
@MyOnlineTrainingHub3 жыл бұрын
Thanks so much 😊
@chrism90373 жыл бұрын
Another great video Mynda, thank you!
@MyOnlineTrainingHub3 жыл бұрын
Thanks for watching, Chris!
@MegaTrickykid Жыл бұрын
very helpful - thank you very much indeed!
@MyOnlineTrainingHub Жыл бұрын
Great to hear!
@mathewinmuscat Жыл бұрын
Awesome!!!!
@MyOnlineTrainingHub Жыл бұрын
Thanks!! Glad you liked it 🙏
@qasimjan5258 Жыл бұрын
Hi Mynda andPhil thanks for the tip. I wanna know if I have many instances of Microsoft like Mcrft, Micrsft, mcrosft etc. Will the transformation table will be able to pick it up.
@datafreak9115 ай бұрын
Nice video. I would like to know how u got that transform table? is it something you already prepared or it was an automatic result?
@MyOnlineTrainingHub5 ай бұрын
It's something I already had.
@shortclipsvideos85733 жыл бұрын
Thanks for this knowledge 😁😁😁
@MyOnlineTrainingHub3 жыл бұрын
My pleasure 😊
@superyngo2 жыл бұрын
Thank you very much!
@MyOnlineTrainingHub2 жыл бұрын
You're welcome!
@mohamedabdellatif14862 ай бұрын
fantastic
@MyOnlineTrainingHub2 ай бұрын
Thank you so much 😀
@elijahchongtao7 ай бұрын
How do i use this fuzzy match to time into unevent group of timing. E.g. 8.01am, 2pm 2.45pm 2.58pm will group to 8am to 3pm shift, while 3.01pm 4.59pm 8.59pm can group into the 3pm to 9pm shift?
@MyOnlineTrainingHub7 ай бұрын
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@felipe41813 жыл бұрын
This are the feature that only Mynda explains, it is so useful! By the way, the link to the file is not working!
@MyOnlineTrainingHub3 жыл бұрын
Thanks, Felipe! The link works for me. If you reach out via email I can send you the file: website at MyOnlineTrainingHub.com
@shrikantbadge397811 ай бұрын
Can I please request you to make some more videos on web scraping on power query? Like the one you made before in this playlist?
@MyOnlineTrainingHub11 ай бұрын
Glad you found our videos helpful! Will keep your topic suggestion in mind 👍
@chocolate_guy2 жыл бұрын
Great video. Thanks. Can you tell me how to create a column that shows the Similarity score?
@MyOnlineTrainingHub2 жыл бұрын
I'm not sure you can in Excel, but here's how in Power BI: docs.microsoft.com/en-us/power-query/fuzzy-matching#:~:text=By%20default%2C%20Power%20Query%20uses,results%20for%20all%20the%20rows.
@chocolate_guy2 жыл бұрын
@@MyOnlineTrainingHub Hi! Thanks for the response. I tried following those instructions, but I can't get the Cluster Values command to show up. I'm using Excel 365, so it should be the newest version. Do you have any idea why it might not be an option?
@MyOnlineTrainingHub2 жыл бұрын
Hi Peter, it's not available in Excel. It's only available in Power BI, which is what that tutorial covers.
@osielgutierrez7837 Жыл бұрын
Greats!
@MyOnlineTrainingHub Жыл бұрын
Thank you!
@bestrecitationofharamainim89512 жыл бұрын
I did not have fuzzy option in power query
@MyOnlineTrainingHub2 жыл бұрын
It’s only available in later versions of Excel.
@markusher16212 жыл бұрын
Is this the same as the Microsoft Labs Fuzzy Lookup add-in?
@MyOnlineTrainingHub2 жыл бұрын
Not sure, Mark.
@georgefandamu51152 ай бұрын
how d you match the positive and negative amounts in excel power query
@MyOnlineTrainingHub2 ай бұрын
You'd have to covert the negative values to positive. I'd add a column for this purpose rather than converting the original values so they're all positive.
@MyOnlineTrainingHub2 ай бұрын
You'd have to convert the negative values to positive, but I'd do this by adding a column that converts them rather than converting the original values.
@philreed16053 жыл бұрын
I'm very keen to get this working, I've wanted fuzzy matching in Excel for years and I have briefly used Power Pivot in the past. Unfortunately, whenever I load a dialog window in Power Query Editor eg 1:36 Merge Query as New, the dialog box is not large enough and it crops the outer c.50 pixels all around. I cannot see or select some of the critical icons/widgets. I've Googled this problem and found no solutions, I've tried disconnecting the external monitor and can't get it to display correctly. Is this something you have come across before?
@MyOnlineTrainingHub3 жыл бұрын
Hi Phil, I've not seen this issue before, but I do recall some fellow Microsoft MVPs complaining of rendering issues. Perhaps try changing the resolution on your monitors. Your current settings may not be compatible with the Power Query window.
@SamehRSameh3 жыл бұрын
I did merge query between 2 tables Every ID has many transaction ,, how can I return first & last date for every ID ?
@MyOnlineTrainingHub3 жыл бұрын
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@usmanyousaf85493 жыл бұрын
using power query how to extract data that contains email addresses as it gives error [Email Protected}. I posted the question on forum .. But no answer. appreciate if you can answer here . THanks
@MyOnlineTrainingHub3 жыл бұрын
Hi Usman, I don't see any posts on our forum from you. Perhaps you used a different name? Please don't reply here. I get so many comments that I won't see any follow up replied to this thread as it'll be buried.
@admiralgeneralaladeen94372 жыл бұрын
Hi Mynda, Thanks for sharing valuable knowledge. I am looking for some help - my datasets are a bit big. One table has 3K rows and the other has 130K rows. I am trying to find fuzzy matches from the 3K table to the 130K table. I tried this with Fuzzy Lookup add-in in excel and also followed your video and tried it using power query... but both methods are taking forever... and even after 15 minutes I don't see any results. Can you please help me? The client's requirement is to do a Fuzzy match because we know there are definitely many mismatches with spelling and other typing mistakes though the products are the same.
@MyOnlineTrainingHub2 жыл бұрын
Have you tried turning off background data previews in the Query Options via the File tab in the query editor?
@parvelinho3 жыл бұрын
Hi, have you worked with Qlik? Is there a big difference between Qlik and PowerBI?
@MyOnlineTrainingHub3 жыл бұрын
I haven't used Qlik, but going by the Gartner Magic Quadrant for BI tools, Power BI is way out in the lead.powerbi.microsoft.com/en-us/blog/microsoft-named-a-leader-in-2021-gartner-magic-quadrant-for-analytics-and-bi-platforms/
@jivasjacopo26373 жыл бұрын
I need to enrol the complete Excel Course. How can go about?
@MyOnlineTrainingHub3 жыл бұрын
Hi Jivas, you'll find our course options and sign up pages here: www.myonlinetraininghub.com/
@mtstans2 жыл бұрын
HI, I have a table with 16 rows and 30 columns of data that equal either -1,0, or 1. I'm trying to match this with another table a larger range of the same data. What I'm trying to do is categorize my data from my new table to old table. Any videos or suggestions for this? I couldn't get it to work with the information presented in this video. Thanks.
@MyOnlineTrainingHub2 жыл бұрын
Hi Matthew, Please see this tutorial: www.myonlinetraininghub.com/easily-compare-multiple-tables-in-power-query If you're still stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@mtstans2 жыл бұрын
@@MyOnlineTrainingHub ahh right on. Thank you so much! I'll report back when I get through it.
@mtstans2 жыл бұрын
Posted a new thread on the forums. Glad I ran into this problems. Haven't looked into yet but seems to be a gold mine for excel tools hidden in there that I look forward to checking out down the road.
@kg19803 жыл бұрын
NAMASTE how can we use feature like pivot table in Power query ....so we don't have to repeat same work daily plz help if u have made any video on that ...plz guide me to the video... thanks very much
@MyOnlineTrainingHub3 жыл бұрын
Power Query isn't designed to build the PivotTable, instead once you close & load the query to the Excel workbook, you can then build a PivotTable from there. Here is a tutorial on PivotTables: kzfaq.info/get/bejne/rLececx9usfbZJ8.html
@kg19803 жыл бұрын
@@MyOnlineTrainingHub thanks for replying i really appreciate your work and your valuable time..
@craigsmall79843 жыл бұрын
Wow. That is all.
@MyOnlineTrainingHub3 жыл бұрын
Glad you liked it, Craig!
@tcrlrunner11283 жыл бұрын
hi there . is there a way to condense 14 different files into 1 file which have all different timers on them
@MyOnlineTrainingHub3 жыл бұрын
I'm not sure what the relevance of timers has.
@tcrlrunner11283 жыл бұрын
@@MyOnlineTrainingHub Im learning by your videos but have taken to google sheets which are online and can be viewed by multiple people , your videos have taught me alot but now as goggle sheets are online unless i have them open they are not always updating my master.
@tcrlrunner11283 жыл бұрын
so the relevance of the timers are for live api pulls and are set at 1 mins to 1 month, the 14 files will feed the master, this is not working due to not all the info updating at once so need them all on 1 file, i just wanted to know is a script possible for 1 files with 14 sheets but with all different timers.
@MyOnlineTrainingHub3 жыл бұрын
Excel has an online version too as shown here: kzfaq.info/get/bejne/jsyXd8mGqryUloE.html Yes, you can get data from 1 file with 14 different sheets. Any timers you might have set up are not relevant from Power Query's point of view. It just gets the data when you click the refresh button. If you have further questions please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@tcrlrunner11283 жыл бұрын
@@MyOnlineTrainingHub thanks again for your reply , ive just added your forums and am sure il be posting a few more questions as im trying to launch what i have and is taking me far too long. love your content as always and thanks again
@soheehan91803 жыл бұрын
Very informative but too fast towards the end. I hope you could explain more step by step instead of keep going.
@MyOnlineTrainingHub3 жыл бұрын
Glad it was helpful. You can change The playback speed by clicking on the cog icon in the bottom right of the video.
@robotmanx20094 ай бұрын
WAY too fast
@MyOnlineTrainingHub4 ай бұрын
You can use the cog icon in the bottom right of the video to change the pace to suit.
@iteefmedia44852 жыл бұрын
You are too fast, and that makes you helpless
@MyOnlineTrainingHub2 жыл бұрын
Most people like the pace of my videos, but if it’s too fast for you then you can slow down the playback speed in the video settings (cog icon in bottom right).
@iteefmedia44852 жыл бұрын
@@MyOnlineTrainingHub thank You, Did So and it was really helpful.