Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX - Help with FIND

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!

 

 

 

 

14 REPLIES 14
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

So the two tables have relationship with each other, right? Could you please share some sample data?

 

Regards,

Jimmy Tao

Anonymous
Not applicable

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

 

 

Anonymous
Not applicable

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.

 

Untitled picture.png

 

@Anonymous ,

 

Change the AuthCode from number type to text and then merge the two tables using Fuzzy matching as below:

Capture.PNG 

 

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.

Anonymous
Not applicable

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).

Nathaniel_C
Super User
Super User

Hi @Anonymous ,
In Power Query, add a Conditional Columnac.PNG

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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





Did I answer your question? Mark my post as a solution!

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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





Did I answer your question? Mark my post as a solution!

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

Anonymous
Not applicable

Thank you! I'll give it a try in the morning and let you know.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.