Forum Discussion
How to extract continuous string set from a column with multiple criteria
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.
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])