Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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!

 

 

 

 

Need Help

14 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you... the two columns are in different tables. can PowerQuery reference a different table?

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        6 years ago

        Hi Anonymous ,
        Sorry, I missed that. Let me revise.
        Nathaniel

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    Anonymous ,

     

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

     

    Regards,

    Jimmy Tao

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

       

       

       

       

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        6 years ago

        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