Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have two tables - one contains our Authorization code [AuthCode], and the other from the vendor contains their version of our auth code. The vendor [Authorization] field is not consistent and sometimes adds letters to the beginning and numbers to the end. I'm not able to accurately parse out the AuthCode from the Authorization in PowerQuery because it's not a constant number of characters that's added, and our auth number isn't a consistent length.
Is there a way to use FIND to find the AuthCode within the Authorization number and then return in a calculated column if found?
Please help. Thank you!
@Anonymous ,
So the two tables have relationship with each other, right? Could you please share some sample data?
Regards,
Jimmy Tao
The tables have a relationship on another field, but only many:many... I'm not able to share the data because it's healthcare data.
@Anonymous ,
Could you please share two simple sample tables? Just add the necessary columns, several rows. So I could do analysis on your issue. Please make sure do not input any sensitive data.
Regards,
Jimmy Tao
The Claim Table contains authorization numbers that have additional prefix/suffix depending on the payer and is not a uniqe list - auth numbers can be repeated.
The Auth table is a unique list and contains our actual Auth number. I'm trying to join if the AuthCode string is found in the Authorization field.
@Anonymous ,
Change the AuthCode from number type to text and then merge the two tables using Fuzzy matching as below:
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I'm not sure how this was marked as the solution... this didn't work for me... I tried adjusting the setting on the fuzzy match a couple of times, and it resulted in 0 matches. (and took forever to refresh).
Hi @Anonymous ,
In Power Query, add a Conditional Column
or if you want a calculated column:
Yes = if(CONTAINSSTRING(myTab[Authorization],myTab[AuthCode]),"Yes","No")
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Thank you... the two columns are in different tables. can PowerQuery reference a different table?
Hi @Anonymous ,
Sorry, I missed that. Let me revise.
Nathaniel
Proud to be a Super User!
Hi @Anonymous ,
Please check this post out. https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/m-p/182148#M79842 ImkeF is the magician of M.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
I think this is probably the way to go... I'm just not familiar enough with M yet to get it to work.
@Anonymous ,
@ImkeF is the amazing magician of m. Maybe she has some time to help.
Nathaniel
Proud to be a Super User!
Hi @Anonymous
you can create a custom column with this code:
Table.SelectRows(Auths, (x) => Text.Contains([Authorization], x[AuthCode]))
This will return a column with the matching rows from the Auth-table.
Make sure to buffer the Auths-table, as this is operation won't be fast.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you! I'll give it a try in the morning and let you know.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |