cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luissimoes Regular Visitor
Regular Visitor

Fuzzy Lookup

One of the most required functionalities in terms of data transformation for Power BI is the ability to do Fuzzy Lookup on two datasets so that input text values with minor errors can still be mapped to a dimension in PowerBI.

I know best practices would redirect this to the usage of a ETL tool and take care of this in a DW etc.... but most uses cases are within the universe of Self Service BI using Excel files.

 

Let me know if there is a workaround either using M, R or other approach.

 

Thank you

7 REPLIES 7
Super User
Super User

Re: Fuzzy Lookup

Why not just use binning?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

luissimoes Regular Visitor
Regular Visitor

Re: Fuzzy Lookup

Binning would require me to know all the values to select... but this is dynamic and tomorrow the items may be different.

Or am I understanding your suggestion wrong?

Community Support Team
Community Support Team

Re: Fuzzy Lookup

Hi @luissimoes,

 

Currently, power bi not support fuzzy lookup. Is only supported to use full query(direct support) and part query(need some additional operations to work through it).  It not support to lookup the similar result(e.g. use "abc" to find out the "aac","a1c") and search with wildcard.

 

BTW, you can submit your requirement to ideas to help us improve the power bi.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
luissimoes Regular Visitor
Regular Visitor

Re: Fuzzy Lookup

Hi Xiaoxin,

I.know that Power BI does not have this support yet... but for example it is possible to use R to do data transformation correct?
So imagine that in the same dataset I have two columns, one with all values and another that would have only the exact matches... Having this, my idea was to use R to fuzzy match one of the columns with the distinct values of the other one (possible values) and add a third column with the results.
Is this possible?

Thanks
Community Support Team
Community Support Team

Re: Fuzzy Lookup

HI @luissimoes,

 

If you meant use r script to deal with original data source, r script support these operations.


I think t-sql fuzzy matching is more simple than r script, you can refer to below links:

Fuzzy Logic function in R as in Matlab

Fuzzy matching using T-SQL

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
DrewBbc Frequent Visitor
Frequent Visitor

Re: Fuzzy Lookup - workaround using Cartesian Product

let
Table1 = #table(type table [value = text],
{ {"spruce"}, {"cedar"}, {"birch"}, {"pine"}, {"poplar"}, {"aspen"},
{"baobab"}, {"larch"}, {"willow"} }),

Table2 = #table(type table [word = text],
{ {"sprce"}, {"cidar"}, {"pane"}, {"aspern"}, {"babab"} }),

AddCol = Table.AddColumn(Table2, "Custom", each Table1),
Expand = Table.ExpandTableColumn(AddCol, "Custom", {"value"}, {"value"}),

FuzzyCalc = Table.AddColumn(Expand, "Prct", each
2 * List.Count( List.Intersect(
{ Text.ToList([word]), Text.ToList([value]) } ) )
/ (List.Count(Text.ToList([word])) + List.Count(Text.ToList([value])))),

SortRows = Table.Sort(FuzzyCalc,{{"word", Order.Ascending}, {"Prct", Order.Descending}}),
AddIndex = Table.AddIndexColumn(SortRows, "Index", 1, 1),
RemoveDupls = Table.Distinct(AddIndex, {"word"})
in
RemoveDupls

Highlighted
DrewBbc Frequent Visitor
Frequent Visitor

Video re side by side compare of Fuzzy App with M Code vs new PowerBI functionality

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 150 members 1,871 guests
Please welcome our newest community members: