Forum Discussion

rajrajsha's avatar
rajrajsha
Frequent Visitor
4 years ago
Solved

Split last characters before the number from column

Hi Team,

I'm trying to split a column that contains text and number into two seperate columns, one containing the number and other contains the character.

Below is the sample data how it looks.. I need to extract numbers and text seperately..

Please ignore if the text character is in the middle of the string. (Ex. 170M01788) 

sourceNumberText
170M01830X170M01830X
016 260423XXX016 260423XXX
170M01788170M01788 
170M02313X170M02313X
002P113748002P113748 
016 273150AM016 273150AM

 

Number and Text columns is what im expecting.

 

Thanks..

 

Need Help
  • Fowmy's avatar
    Fowmy
    4 years ago

    rajrajsha 

    You can do it two steps:
    Choose Digit to Non Digit

    Then choose Merge Columns under Trasnform tab after selecting 1st and 2nd columns


    Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ38DUwtDA2iFCK1YlWMjA0UzAyMzAxMo6IgIhAFJhbWCB4RsaGxlDlBkYBhobG5iYWCN3mxoamBo6+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [source = _t]),
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "source", 
Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"source.1", "source.2", "source.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"source.1", "source.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Source")
in
    #"Merged Columns"

    Result

     



2 Replies

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

    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("i45WMjQ38DUwtDA2iFCK1YlWMjA0UzAyMzAxMo6IgIhAFJhbWCB4RsaGxlDlBkYBhobG5iYWCN3mxoamBo6+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [source = _t]),
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "source", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",List.RemoveLastN(Table.ColumnNames(#"Split Column by Character Transition"),1),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Number"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{List.Last(Table.ColumnNames(#"Merged Columns")), "Text"}})
in
    #"Renamed Columns"

     

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    rajrajsha 

    You can do it two steps:
    Choose Digit to Non Digit

    Then choose Merge Columns under Trasnform tab after selecting 1st and 2nd columns


    Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ38DUwtDA2iFCK1YlWMjA0UzAyMzAxMo6IgIhAFJhbWCB4RsaGxlDlBkYBhobG5iYWCN3mxoamBo6+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [source = _t]),
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "source", 
Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"source.1", "source.2", "source.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"source.1", "source.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Source")
in
    #"Merged Columns"

    Result