locked
how to split a column based on alphabets and numbers RRS feed

  • Question

  • hi all

    i have a column names description of type text ..i have data as below;

    Description

    VANKHAD

    EROS

    1011227#INFRASTRUCTURE

    1523183#SECURITY

    2144521BUILDRELEASE

    NOW, I NEED TO SPLIT into 2 columns as code into 1 column and text into other column

    o/p should be 

    Code              Description

                         VANKHAD

                         EROS

    1011227       INFRASTRUCTURE

    1523183       SECURITY

    2144521       BUILDRELEASE

    I achieved this to some extent

    i got o/p correctly for all but for 2144521BUILDRELEASE this row i didn't get properly, coz i splitted the columns using #,

    here 2144521BUILDRELEASE for this i don't have # to split. 

    how can i do this .Please help..urgent..

    thanx in advance..


    lucky

    Tuesday, June 20, 2017 7:30 AM

Answers

  • Try the following, substituting for your actual source step:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
        AddedCode = Table.AddColumn(Source, "Code", each Text.Combine(List.Select(Text.ToList([Description]), each Value.FromText(_) is number))),
        AddedDescription = Table.AddColumn(AddedCode, "Description1", each Text.Combine(List.Select(Text.ToList([Description]), each Value.FromText(_) is text)))
    in
        AddedDescription

    Tuesday, June 20, 2017 7:14 PM