Forum Discussion

Till__'s avatar
Till__
Helper I
3 years ago

Splitting Columns without generating new column

Dear Community,    I want to split serveral columns after if it changes from number to text. For example the entry is 2xyx and I only want to have the 2 left over. Test 2xyx Normaly a ne...
Need Help
jgordon11's avatar
jgordon11
Resolver II
3 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(
        "LVRHtuAgDLvLX8+CEtpZ5s0iFQLppACnn9j5Oz8SZElY/vv3h1P11HP/8+eHcRH3Ob8V
        VcSnxkFFud3uBipCSvDXW1WELauNb8WJsrG0P//+/P3R2ji35e9P57v9rUwlY2P9W2nNb
        fE1fDViqpsOzqi08ZngrDLHbS/EqQSp1+uA75z1a4SOsrrOERpWit/XCdBUa7tN41spXa
        U0QZNKyngP7qNjyBFmCyQ1b0IBYkyqbkUxhunSjy0KpJvPIF9qumwRuhghlyd8sgzjITe
        Aw5jd4wqfCR2X7oSG1JyHR31Mb+swYRO2788MFSWrbXqEYZo9pcApVyJ2V/9JSG3AO0J3
        WxreShDVPi0gcsFPGwqcSeeaGmEEFX0bazQnpRrkG67sPSNtpbp4BuBVhfHY4KrgU/bzZ
        6bzd0QUTs3RW6AgGEluh0oJlutcozw2DD3QUkrdSywIaIq34JygNNmSPjaMjV3/gBQm8r
        kl+NOMU3OhTt1ui0cSurUPOMc0fcXBBfVOXTuun8Wc9vPR4DzpOJ8t3qH5HKC1ktXkFtR
        HRS4OECVX5z3e6BzpN3cgjuRsnxJMDjN6XkOHONXoWzBA8Kr2ccJZlHNXyleV9CQcEmIv
        Hz4+UvRdAb5CvX1SwJFgLnuHLrP1wBwoQge/B6zItkTQxQ1ru/zZTMn0zNimMmGYEJDop
        cWR1xUNZQGprFLl8ugy4925NegiTaf7DYShgzthOIymZ67hDpd02PsaKVJnrxtv+3cs0U
        Xj5g2nSZrNNs8vDFlCsJg2Yo+x/yZ52TYcjzd1Y9kRkIeE6ZZMFZsD7gN2zHn6Hp0IV9+
        AYwzb+7Jh4mUcbvTpHbMmrZgNdU7pQueVi/eCbylqX3/uVJLv/jc75pwH//VOO4aaUnJ1
        MX/Jap8DnRf6mWuoFCOHe6f/2zvV+hwF88aX/ODLGGH7+0CObZ42jJNocpO+VZSPcn6bw
        y9N+JVl0mUxjITO090hjFrvES3Tqj3nHWVxv9VITNBxqEGW0KSd3WezVCbMuKsYleuSLK
        ZMRdvgwkhxghuU63q7cKPpuT0TBov0Mb/L699/", 
        BinaryEncoding.Base64), Compression.Deflate))),
    // list of column names to operate on
    tcn = Table.ColumnNames(Source),
    c = Character.FromNumber,
    // non-numeric character list
    nn = List.Buffer(List.RemoveItems({c(0)..c(255)}, {"0".."9"})),
    TransformOperations = List.Accumulate(tcn, {}, (s,c)=> s & 
        {{c, each Number.From(Text.Start(_, Text.PositionOfAny(_, nn)))}}),
    Result = Table.TransformColumns(Source, TransformOperations)
in
    Result