Forum Discussion

MarcusR1's avatar
MarcusR1
Helper II
3 years ago

Using Python Regex to look for values in column

I'm trying to add a column called ofInterest select postcodes that match RG, OX & SA, the column would contain a 1 if the match is found, or 0 if not found.
My code is:

 

import pandas as pd
import re
dataset["ofInterest"] = dataset["RegAddress.PostCode"].re.search("[SRO][AGX][0-9]")

 

I'm sure I've got this all wrong because I have very little experience of Python (I know REGEX quite well) - can anybody help me?

Need Help

6 Replies

  • MarcusR1's avatar
    MarcusR1
    Helper II
    3 years ago

    Thanks Amit, I have posted it on the Python forum. I think it's still relevant here though, because the Table/Column structure is relevant to PowerBI, and hopefully if I find a solution it will help other PowerBI users.
    As an aside, it would be great if PowerBI supported REGEX in DAX or PowerQuery ðŸ˜‰

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi MarcusR1 ,

    According to your description, here are my steps you can follow as a solution.

    (1)This is my test data. 

    Table:

    Table(2):

    And the relationships between tables:

    (2)We can create a measure.

     

    ofInterest = var _t = {"OX","RG","SA"} return IF( MAX('Table'[Type]) in _t , 1 ,0)

     

    (3)Then the result is as follows.

    Please refer to the following documents that may be helpful:

    Using regular expressions in power bi desktop - Microsoft Power BI Community

    Regular expressions in Power Query – Q-stat

     

    Best Regards,

    Neeko Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi MarcusR1 ,

     

    Has your problem been solved?If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

     

    Best Regards,

    Neeko Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

    • MarcusR1's avatar
      MarcusR1
      Helper II
      3 years ago

      Hi Neeko, unfortunately your solution doesn't work as REGEX becasue I need to look for any of the letters follwoed by a number (regexz /[SRO][AGX][0-9]/) but your suggestion doesn't do that.
      all the best, Marcus

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi MarcusR1 ,

         

        I'm sorry I can't understand your needs, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

         

        Best Regards,

        Neeko Tang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.