Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Just use List.First instead of Text.Combine (and delete the , ",")
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
Proud to be a Super User!
Perfect thanks everyone
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?
On the Transform tab under Format, you can UPPER or lower case one or both columns.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Just use List.First instead of Text.Combine (and delete the , ",")
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |