Forum Discussion
Power Query searching for text between 2 columns
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
Just use List.First instead of Text.Combine (and delete the , ",")
Regards,
Pat
6 Replies
- mahoneypatMicrosoft Employee
Just use List.First instead of Text.Combine (and delete the , ",")
Regards,
Pat
- AnonymousNot applicable
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.
- PhilipTreacySuper User
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
- AnonymousNot applicable
Perfect thanks everyone
- AnonymousNot applicable
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?
- mahoneypatMicrosoft Employee
On the Transform tab under Format, you can UPPER or lower case one or both columns.
Regards,
Pat