Fuzzy match / merging in Power BI Desktop (October 2018)
HTML-код
- Опубликовано: 16 окт 2024
- In this video, we look at the new fuzzy match / merging option within the October 2018 version of Power BI Desktop. This feature is incredibly powerful when it comes to data matching and can be adjusted to meet your needs.
LET'S CONNECT!
Guy in a Cube
-- guyinacube.com
-- / guyinacube
-- / guyinacube
-- Snapchat - guyinacube
-- / guyinacube
**Gear**
Check out my Tools page - guyinacube.com...
#powerbi #powerbidesktop #guyinacube
You guys rock, love your PBI videos. They have become my go-to when I need to learn new techniques or functionality. Thanks!
Love your videos. Your energy/personality and training style is easy to follow and VERY MUCH appreciated! Keep it up.
Great Videos Guys. Thank you for helping me to get my Power BI certification done. I am writting this from New Zealand and the Desktop Image in the background is just 2 hour drive from my home. It's called "Wharariki beach"
It's a good explanation Patrick!
It'd be great to know more about the % accuracy and how that works rather than doing a bucketload of trial and error.
Thanks for the video, great as always.
I found 0.95 works best. The default of blank, which is 0.8 is a bit too fuzzy and you get too many false positives. (The range is 0 - 1 where 1 is the same as a normal join)
Can the transformation table contain multiple entries? e.g. Jim -> James, Jim -> Jamie etc.
Great features! Thanks Patrick for good & simple review as always! i was stuck with the same problem during comparing of two tables (nested join)... i used to make lower case first, than trim, clean... after join.
Sooo usefull video, thansk a lot . Hugs from colombia 🇨🇴
Thanks for helping me understand that transformation table. The Microsoft hit/tip isn't at all helpful, but this video got me through.
Great addition to Power BI Desktop. Looking forward to having it GA together with Composite Models and PDF parsing.
Awesome!
nice! loved this guy's energy. Keep it up team!
So the transformation table has to have headers of to and from? what if there was a join going the other way also? To be safe do we need to have "William" and "Bill" on both sides in the table? Thus "William" & "Bill" AND "Bill" & "William" ?
Correct. From and To. If you choose to use it.
This is a super great addition, especially when working with data coming from those pesky .csv, .txt and excel files filled in by people who got no clue whatsoever :D)
OUTSTANDING yooooo
YOOOOO! :) Love it! Thanks for watching.
Great video! I'm learning so much; love your genuine enthusiasm. Is there a way to reach out to ask a question?
This is awesome. Thank you!
This is super cool, i had to do separate Error reporting for the typo earlier to fix this kind of issues where i was using anti joins to report the errors. But now i don't need to worry. Thanks Pat Fuzzy Matching with Patrick for this demo :)
Great tools! I have trouble with slow refresh and query application. I have about 3000 records to match with other table of 7000 records, and it takes hours! Any suggestions from where to start?
Can you merge fields across multiple data sources to create a master Dimension table?
This is great. Thanks for another top video! I've been trying this tool quite in detail and still impressed on how the algorithm performs (I think that 0.90/0.95 is the way to go to do not get many false positives) I'm currently testing this tool with a PBI project which is connected to a big data set and looks like is taking ages to run...would love to hear some insights on bigger scenarios. Seems that you know the team behind this product..is there any way to reach/contact them? Thanks!
Great to hear it has been working well for you. I would imagine that with larger datasets it would take longer. Doing lookups and comparison over a large amount of data could be both memory and processor intensive. I don't know of any trick to optimize that really. Maybe post it in community.powerbi.com? The engineering team hangs out there. along with a bunch of other folks that may have suggestions.
@@GuyInACube Thanks for your quick response! Yup, I've posted in there early this morning. Let's see if somebody replies. Thanks
@@joaquinarroyo315 Good deal.
Hi,
Thanks for great help. You video is actually helping me learn power bi. Have one limitation in power bi i couldn't get workaround for.
How could i filter data in other sheets of my report based on a scatter plot in my landing page. There is report level slicer i read about but i need scatter plot to filter the data
Respect brother
Hey Patrick, great video, thank you for that, i need to show the result on the dashboard and provide the functionality of search to the user.
Example : On the dashboard I need to have a search option " Search hear what you need"
The fuzzy works in the background and find the approx. match and displays the results on the dashboard, like on the websites we do.....
Please reply "Urgent" support....
Great video, thanks
I wonder what algorithm is used to calculate similarity (or diffeence) between strings in Fuzzy Merge. Is it the good old Levenshtein, Longest Common Substring, or something else?
Can you do fuzzy joins in Excel when joining tables in the data model? I want to join multiple tables for my Powerpivot but I need to use fuzzy matching because I can't match the data exactly. thanks
What if the name is a 3word name (e.g. Chris Down Michael & Michael Chris Down) in the 1st table and in the 2nd table the name i have only 1word names (e.g. Chris). How i am gonna merge this? There is match in both rows of table 1. Thanks!
Can fuzzy map work with making relationship between data, other than merging
can you make a video to compare a string with a numeric value using if statement or switch statement please
Nice. 😁😁😁
I hope the Ignore Wide , and Ignore Kana to Fuzzy Match as Japanese.
These Ignore feature exist in SQL Server Collation feature.
and posted as Power BI Ideas. what do you think?
Hi， I have been looking for the option then preview. i just can't find the fuzzy merge option. i am using the june 2019 version. has that been removed ? Thanks man
Cool shirt... Where can I get one?
What we do before? Use Fuzzy Look-up in MS Excel. It is good to know that it is available in PBI.
Agreed. very happy to see this land inside of Power BI!
fuzzy match is just so fun now when @patrick has done his.
haha thanks! And, thanks for watching! Glad you enjoyed it.
Where does he get those wonderful toys.....er shirts? -Joker, probably
That shirt was one given away at conferences and there was some variant of the public Microsoft eCommerce store (not the actual Microsoft Store) where you could purchase shirts, and that was one of them. www.microsoftmerchandise.com/Shop/#/ I don't see them listed any longer though :(
boooooo
Before: almost checking one by one...now PBI!
yup :) love it.
Best resolution for dev in power bi?? 1080p? 2k? 4k? wide?
I would say nothing less than 1080p. I personally have two 34" Ultra-wide monitors. Regardless of what you have, try to target 1080p. I know Power BI Desktop does some scaling, but know that most people probably have some variation of 1080p. So, don't develop with your screen resolution and think it will look great for everyone.
@@GuyInACube thks!!! I dev in 1080p. But I am thinking to jump to 34'' ultra wide.
1.How can I load multiple files with multiple sheet,column order can be different in different pages.2.how to load multiple file type as well as from table data into a single power bi dataset.3.i have 4 power bi desktop file which has same kind of data for 4 different periods (import mode). How to load into single pbix file from all 4 pbix file.4.how to load data from multiple power bi datasets to single power bi desktop .5.i have one complecated question ,which is unanswered in power bi community.can you pls tell me your username in community that I can tag you there.
you guys made all kinds of T-shirts with Power BI logo/sign
Can I use the Fuzzy match, to match any work in a sentence, like: match the work “Corolla”, in a sentence , “xei corolla dsl 15”.
You could set the Similarity Threshold lower to see if that works. By default it's set to .8, which requires a pretty high similarity between the two values. Lowering it could possibly increase the possibility of a match.
Guy in a Cube BANNGGG!!!! .6 is the number!!! Tks!!
Hey from where you are buying those t-shirts ?
The one in the video was from the Microsoft store. But, i don't see it any longer :( other shirts are random purchases.
Hi ,
I tried something to get multiple select option in drill through. Although the output wasn't beautiful the underlying objective was achieve. Can you please have a look ? Email address ?
i gave up after going to the cinema and return....BI still runnning the query
You are a crack man! Thanks!
Yoooooo!
DQS in Power BI
Not quite. but getting there.