Power Query searching for text between 2 columns

[Anonymous]

Hi all,

2 part question, hopefully pretty simple.  I have 2 tables, I'm trying to search a column in 1 table for values in a column on a second table.  I want it to return the matched value when one is found, but only return the first hit on my list.

I'm searching Table 1 field [Description] for values in a column called [Tag] from a table called Symptom.

=let myvalue=[Description]
in
Text.Combine(
    Table.SelectRows(Symptom,
each Text.Contains(myvalue,[Tag]))[Tag]
,
",")

This code lists all hits with a comma separation, but I can't work out how to change it so it only returns the first hit.

For bonus appriciations my next step is to return the value in the second column of the Symptom table.... 

Table is simple 2 columns:

Tag             Output

Red             It's red

If I was searching a decription field value of "Is it red".  It would look for the value 'Red' from the tag field and return 'it's red' from the output field.  If that part can't be done easily I can do a lookup in DAX if I can get the first part right.

Many thanks

[PhilipTreacy]

Hi @Anonymous 

Specify Comparer.OrdinalIgnoreCase in Text.Contains

Text.Contains(myvalue,[Tag]), Comparer.OrdinalIgnoreCase)

https://docs.microsoft.com/en-us/powerquery-m/text-contains

Regards

Phil 

[Anonymous]

Perfect thanks everyone

[Anonymous]

@mahoneypat 

I figured out why it's hit and miss....which leads me to a newcomer question... how would I make that code so it isn't case sensitive?  

[mahoneypat]

On the Transform tab under Format, you can UPPER or lower case one or both columns.

Regards,

Pat

[mahoneypat · Accepted solution]

Just use List.First instead of Text.Combine (and delete the , ",") 

Regards,

Pat

[Anonymous]

@mahoneypat 

Thanks for the speedy reply.  Looks to have helped a lot, I also figured out the second part of the question.  Still a leftover issue if you could help.

My list of search words list in a specific order to get the right result.  EG -

Text to be searched - "User has disconnected"

My list is ordered "disconnect" then "connect".  That should make it flag as 'disconnect' rather than 'connect', but it seems hit and miss which the code is finding.