Forum Discussion

Nivedhana's avatar
Nivedhana
Helper I
2 years ago
Solved

Split Alphanumeric column into 2

We have a column capturing data in below format. Is it possible to split the text and numbers in 2 different columns in power query editor?

 

ABC
1
2

Bhy

3

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago
        r = Table.TransformColumns(
        Source, 
        {"ABC", (x) => 
            [num = try Number.From(x) otherwise null,
            txt = if num = null then x else null]}
    ),
    expand = Table.ExpandRecordColumn(r, "ABC", {"num", "txt"})
  • slorin's avatar
    slorin
    2 years ago

    Hi,

     

    Another solution

    = Table.SplitColumn(
       Source,
       "ABC",
       (x) => if Value.Is(x, type number) then {x,null} else {null,x},
       {"Num", "Text"}
     )

    Stéphane 

3 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago
        r = Table.TransformColumns(
        Source, 
        {"ABC", (x) => 
            [num = try Number.From(x) otherwise null,
            txt = if num = null then x else null]}
    ),
    expand = Table.ExpandRecordColumn(r, "ABC", {"num", "txt"})
  • slorin's avatar
    slorin
    Super User
    2 years ago

    Hi,

     

    Another solution

    = Table.SplitColumn(
       Source,
       "ABC",
       (x) => if Value.Is(x, type number) then {x,null} else {null,x},
       {"Num", "Text"}
     )

    Stéphane