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.
Greg_DecklerI have attached a final Client name file. The operation needs to be done on this fine. Ongoing project List remains the same. My results on this final client file returns some unwanted data like Anders,Unit,Con,Comm,National,Nor,Tri,Ste,Mar.
This is where I see that the threshold does not be effective. Not able to think of a way to improvise on the formula to eliminate these errors.
The good note is that the retuned blank values are perfectly the way they should be since there are no matches for them in the Project list.
Greg_DecklerThe logic I think will work is , instead of giving the matches by taking both the Client name and project columns into consideration, can we only take Project column into consideration and provide the maximum match term with project column from the client name?
Example:
Client Name Project Name
National Grid National Grid
First National ABB
Operation to follow as below:
Take National Grid for consideration.
Check for 'N' in all cells of Client Name and save this list in a variable.
Check for 'Na' in this saved list and update this variable with the new lesser list.
Check for 'Nat' in this newly saved list and update the variable......... and so on until we check for 'National G'.
We have to stop with the first character after the first occurance of space because if not then the return values will need to be an exact match with the client name.
This above logic will ensure that National Grid is mapped only to National Grid and not First National.
Now, The above step is iterated by taking ABB into consideration.
This way we can ensure that every Project Name is mapped exactly to one item from the Client Name and the retun value will be the Client Name and not the matching strings.
This was my initial proposal for the PHP code but I am not sure how I lost the course on this. Probably I got overwhelmed with the results I recieved with DAX.
Is there a way to try the above logic with PBI? This seems to be fool proof atleast for the current set of Client Names and considering the way they appear, it could work on any additional clients that may add in to this list.
I need to submit this presentation in another 12 hours , i.e 12:00 PM. Hope this method is possible.