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 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

     



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