Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Power Query - Spilt Column From Digit to Non-digit Once

Hello,   I need help with Power Query for something. I have a column that has both addresses and 6-7 digits student ID at the end of each row for that column, and I need to split them out into ...
Need Help
Show and Tell
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
2 years ago

How about splitting by the first space from the right side? You can do that with just the GUI.

 

You could also do it as two custom columns.

Address = Text.TrimEnd([#"Address & ID"], {"0".."9", " "})
ID = Text.AfterDelimiter([#"Address & ID"], [Address])

 

Full sample query:

let
    Source = Table.FromRows({{"ABC3 DEF Road 123456"}}, {"Address & ID"}),
    #"Added Address" = Table.AddColumn(Source, "Address", each Text.TrimEnd([#"Address & ID"], {"0".."9", " "})),
    #"Added ID" = Table.AddColumn(#"Added Address", "ID", each Text.AfterDelimiter([#"Address & ID"], [Address]))
in
    #"Added ID"