Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Checking string of certain format

Hey Everyone, I am new to power query and this has had my head scratching is there anyway I can solve this?

 

Is it possible to check a string dynamically where AA****...BB where AA  and BB are alphabets while ****... represents any amount of integers. If that is possible i would like to return it to an new column

 

eg:                                Output

AB123456BC 3033       AB123456

AB1234BC 2022           AB1234

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use this

    = Splitter.SplitTextByCharacterTransition({"0".."9"},{"A".."Z", "a".."z"})(Text.SplitAny([Input],"/-"" """){0}){0}

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Use this in a custom column where Input is column name

    = Splitter.SplitTextByCharacterTransition({"0".."9"},{"A".."Z", "a".."z"})(Text.Split([Input]," "){0}){0}

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQyNDI2MTVzclYwNjA2VorVgYkBRYwMjIwgIoZOzi5uSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Splitter.SplitTextByCharacterTransition({"0".."9"},{"A".."Z", "a".."z"})(Text.Split([Input]," "){0}){0})
in
    #"Added Custom"
    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      4 years ago

      Hey Thanks, this works but there is a small issue.

      Sometimes the records are as such 

      AB1234/01BC

      AB1234-01BC

       

      how do i extract the above for that type of records

       

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        Use this

        = Splitter.SplitTextByCharacterTransition({"0".."9"},{"A".."Z", "a".."z"})(Text.SplitAny([Input],"/-"" """){0}){0}
  • AlB's avatar
    AlB
    Community Champion
    4 years ago

    Hi there

    Place the following M code in a blank query to see the steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQyNDI2MTVzclYwNjA2VorVgYkBRYwMjIyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Column1 - Copy", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1 - Copy.1", "Column1 - Copy.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Column1 - Copy.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1 - Copy.1", "Output"}})
in
    #"Renamed Columns"

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.