Forum Discussion

deadshotblue1's avatar
deadshotblue1
New Member
3 years ago

Custom Column - IF Statement to pull out specific text

Hi,

 

I have a column in Excel that includes ID numbers. The format should be 3 letters starting with M, then 2 random letters then 9 numbers. Sometimes, people input some letters, numbers or symbols before which is incorrect so I want to remove these. I want the column to pull out the letter M and 11 characters after it.

In Power Query, under Custom Column, I tried a number of queries including IF statements but they did not help. Would anyone be able to help please?

 

Thank you in advance

Need Help

3 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    3 years ago

    Please share a representative data sample (does not have to be real, but should show similar variations), to better illustrate your problem. Also, what platform are you using? (eg Excel, Power Bi Desktop, something else?)

  • deadshotblue1's avatar
    deadshotblue1
    New Member
    3 years ago

    Hi, thank you for responding. I am using Excel and within it, Power Query. I have posted the example data below. The ID number on the first line is the correct format (MHJ901234573) which is 3 letters (starting with the letter M and two other letters after it) then 9 numbers after. Also, any line that is not in that format should be removed like in line 5. Thank you

     

    First NameSurnameID
    PhilSmithMHJ901234573
    AnnaStevensK/MDF803926173PX
    JohnLongUIMQY736251437
    SarahSimonsNC731
    PeterJohnsonH/MGS837462510/GH
    • ronrsnfld's avatar
      ronrsnfld
      Super User
      3 years ago

      You can use a Regular Expression to extract the ID into a separate column (or transform the same column, if you prefer).  Where there is no match, the function will return a null and you can filter that out with Table.SelectRows.

       

      To add the custom function, paste the code below into a Blank Query (Advanced Editor), then rename that query "fnRegex"

      //see http://www.thebiccountant.com/2018/04/25/regex-in-power-bi-and-power-query-in-excel-with-java-script/
// and https://gist.github.com/Hugoberry/4948d96b45d6799c47b4b9fa1b08eadf

//RENAME: fnRegex

let   fx=(text,regex)=>
    Web.Page(
        "<script>
            var x='"&text&"';
            var y=new RegExp('"&regex&"','g');
            var b=x.match(y);
            document.write(b);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}

in
fx

       

      You can then Add A Custom column with Regex= M[A-Z]{2}\\d{9}

       

      full M Code without filtering the null

      let
    Source = Excel.CurrentWorkbook(){[Name="IDtbl"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First Name", type text}, {"Surname", type text}, {"ID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "extracted ID", each fnRegex([ID], "M[A-Z]{2}\\d{9}"))
in
    #"Added Custom"