cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pandadev
Post Partisan
Post Partisan

Look for match in column from list of values in another table

Hi I am trying to find a solution to filtering out people from a list of company names in PowerBi

I was looking to use a match formula , so if any of the names contained LLC , Ltd , Bank , Company , Co at the end of name. Then mark them as match , example of tables

Companies

John Smith

Joe Bloggs Ltd

Bill Smith Co

D Evans

Brian Jones Bank

 

I wanted to match 

Joe Bloggs Ltd

Bill Smith Co

Brian Jones Bank

 

I have 100,000 rows of data to look at , so was hoping i could put in some match formulas

3 REPLIES 3
Mariusz
Community Champion
Community Champion

Hi @Pandadev 

 

You can add a Custom Column in Power Query editor.

image.png

List.ContainsAny( 
        Text.Split( [Companies], " " ), 
        { "LLC" , "Ltd" , "Bank" , "Company" , "Co" } )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

is it possible to replace the 

{ "LLC" , "Ltd" , "Bank" , "Company" , "Co" } )

with a table which has a list 

 

 

Thanks , is it possible to have this so it is not case sensitive as some are typed LLC and some Llc , Ltd , LTD

 

and is there maybe a seperate column i can include for looking at last letters for match , as If I had Co , and I had somebody called Colin Smith , it would wrongly flag that person as a company , but if it was Colin Smith Co , then I would like to include as it had Co at the end.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!