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 alp...
  • 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}
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}