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.
I'm trying to create a new column based on partial text matches in my first column when compared to my reference table.
My Locations reference table is set up like:
Locations
City | State | StateAbv |
augusta | georgia | ga |
detroit | michigan | mi |
cleveland | ohio | oh |
The strings would be:
Table1
Words |
This is and example string cleveland |
A Detroit Example |
augusta value here |
The end result would be:
Table1
Words | Location |
This is and example string cleveland | Cleveland |
A Detroit Example | Detroit |
augusta value here | Augusta |
What would be the best way to achieve this in DAX or M?
Thanks!
Solved! Go to Solution.
Hi @tkcsp ,
Please check if the result below meets your requirements.
Steps:
For details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @tkcsp ,
Please check if the result below meets your requirements.
Steps:
For details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @tkcsp ,
I would prefer M.
Try a fuzzy matching
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
This solution works for my original question, but I was hoping I'd be able to take the answer and extrapolate on it and I think fuzzy matching presents issues with state abreviations. In the data there will occasionally be references to state names and abreviations and some states share city names (e.g. Columbus, GA and Columbus, OH). Also, I could see an issue with the state Indiana being abreviated as "in" where the phrase "in" will also be used as a preposition.
Updated Locations Table:
City | State | StateAbv |
augusta | georgia | ga |
detroit | michigan | mi |
columbus | georgia | ga |
columbus | ohio | oh |
evansville | indiana | in |
Table1
Words |
This is an example string for ohio by cleveland |
an example in detroit |
augusta ga value here |
example for cleveland |
evansville in value |
Expected end result:
Table1
Words | City | StateAbv |
This is an example string for ohio by cleveland | cleveland | oh |
an example in detroit | detroit | |
augusta ga value here | augusta | ga |
example for cleveland | cleveland | |
evansville in value | evansville | in |
Would fuzzy matching be able to pick up on these nuances?
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |