cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@deepakramamurth - 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. 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

52 REPLIES 52
Highlighted
Super User IV
Super User IV

@deepakramamurth - 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])

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted

@deepakramamurth - 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])

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted

@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).

Highlighted

@deepakramamurth Check out my second reply, I proved the technique will work, there is an attached PBIX file.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted

@Greg_Deckler  Thank you so much for the solution. It almost fizes my issue. But I just need a small extension to this. I apologise for not mentioning it earlier.

 

Table A                                                        Table B

BARCALAYS Corporation Ltd                       Barcalays (LLP)

CaSey's                                                         CaseyLLP

 

I need the result to be retrieved in the result columns as "Barcalays" -> case insensitive and just like how the search field works in an excel filter. For Caseys the result should be "Casey".

 

Highlighted

@deepakramamurth - I believe, see updated PBIX attached.

 

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])

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted

@Greg_Deckler  Hi Greg, I dont see any changes in the latest PBIX. It is same as the earlier one. The parent tables are also the same as the earlier one with no changes for Barcalays (LLP) and CaSey. Probably you missed on attaching the updated PBIX.

Highlighted

@deepakramamurth - Not sure, I thought I attached the one attached here. Here is the screen shot:

Greg_Deckler_0-1599419133947.png

I just saved and attached again.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted

 Ge

@Greg_DecklerI dont see any changes in this screenshot as well (even checked the file).

Here is the exact problem statement.

Replace Table 24(a) Barclays with Barclays (ABC)

Replace Table 24[Client Name] Casey's General with CaSey General

Replace Table24[Client Name] ASTRAZENCALimited with 2020 ASTRAZENCALimited AMT

 

Now I want the result to show as Barclays, Casey, Astrazenca

The result should be the complete set of matching characters including space and special characters if any and not the value from the Table 24(a). (For ex: Casey)


I am not really sure if we need to implement fuzzy search. I have not explored much of it.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors