Forum Discussion
How to extract continuous string set from a column with multiple criteria
Hi,
I have a Column A with client names.
Table 1 Table 2
Client Name Ongoing Projects
Microsoft Corporation Ltd Microsoft
BARCALAYS (LLP) Barcalays
Caseys General Casey's
LLP Ernst and Young Ernst and Young
ASTRAZENCALimited Astrazanca
I want to search and extract the client names from Table 1 as below:
Result Column
Microsoft
Barcalays
Casey
Ernst and Young
Astrazenca
Basically the logic I am looking for is as below. The function/formula should check for continuous sequence of characters from Table B in Table A and return that entire sequence. Also this search should be case insensitive (For ex: BARCALAYS and Barcalays) and also the search text can be in between the within text column anywhere and not necessarily at the beginning (Ex: LLP Ernst and Youngand Ernst and Young).
One important criteria is it should return matches for continuous string greater than 2 (i.e; 3 and above).
I tried various formulae like Search, Find, Minx, Left but I am not getting the desired result with the above set of criteria met. Please help me with the logical formula for this.
Happy to give any additional info pertaining to this.
Anonymous - OK, I updated this with a Client Name output version and Project output version in 2 additional columns. I checked the matches, don't see anything obviously amiss. Solved?
PBIX is attached.
53 Replies
Anonymous - All kinds of confused on this one. You reference Table 1, Table 2, Table A, Table B. Is it supposed to do fuzzy matching with Casey's matching Caseys? Where are you creating this new column? Is it a new column? Is it a table? For case insensitive searches, use SEARCH. You can probably get there using Text to Table but I would need some clarification from you to be more specific than that.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Text-to-Table/m-p/1312929#M594
Might be a solution using just SEARCH as well where you get all of the rows from Table 2 (Table B??) and you ADDCOLUMNS with a SEARCH like this:
Column in Table 1 (Table A?) = VAR __ClientName = [Client Name] VAR __Table = ADDCOLUMNS( ADDCOLUMNS( 'Table 2/B?', "Search",SEARCH([Ongoing Projects],__ClientName,,0) ), "Result",IF([Search]>0,MID(__ClientName,[Search],LEN([Ongoing Projects])),BLANK()) ) RETURN MAXX(__Table,[Result])Anonymous - I confirmed that the SEARCH approach will work. See Table (24) and Table (24a) in attached PBIX below sig. Made a small edit to get the information in the format you wanted:
Column = VAR __ClientName = [Client Name] VAR __Table = ADDCOLUMNS( ADDCOLUMNS( 'Table (24a)', "Search",SEARCH([Ongoing Projects],__ClientName,,0) ), "Result",IF([Search]>0,[Ongoing Projects],BLANK()) ) RETURN MAXX(__Table,[Result])Greg_Deckler Thanks for your reply. Lets say table 1 and 2 are Table A and B respectively. I do not need to do a fuzzy search. Casey's shourd return just Casey since the first 5 characters are matching with the search text.
I need to create a new column (Result) and not a table which retrievs the searched value. Lets say this new column is to be created in Table A beside the 'Client Name' column. So it picks up the search terms from Table B, searches for a continuous string match with the 'Client Name' column and fills in the 'Result' column.
Not a fuzzy search but a continuous string search (Exactly just like how a filter search on a column in excel works).
Anonymous Check out my second reply, I proved the technique will work, there is an attached PBIX file.