Split Column to separate digits and characters all stored as data type 'TEXT'

[Anonymous]

Hello,

I have a column that contains username and employee code all stored in format 'Text'. I wish to separate username and employee code to different column so that I can build relation to another datasets.

Username are all characters and employee codes are all digits.

Could you help me how to achieve?

[Greg_Deckler]

Sample data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Adding Sample data,

Data,

Login IDs
abc
def
ghi
123
456
789
jkl
987
654
mno
pqr

Expected Outcome.

Login IDs	Username	Employee Code
abc	abc	null
def	def	null
ghi	ghi	null
123	null	123
456	null	456
789	null	789
jkl	jkl	null
987	null	987
654	null	654
mno	mno	null
pqr	pqr	null

Previous recommended action,

Hope this helps to understand the query.

[rajendraongole1]

Can you check the below link

https://community.powerbi.com/t5/Desktop/Split-column-based-on-text-and-numerical-characters/td-p/13...

To get null values you have define it in add custom columns.

[rajendraongole1]

In power Query , we have option in transform tab with split column there can you can segregate by Nondigit to Digit.

Just check the option we can seperate both text data and digit data with seperate column.

if it clears the problem Please mark it as solution.

Thanks

[Anonymous]

thanks @rajendraongole1 

tried with this formula

= Table.SplitColumn(#"Renamed Columns1", "LOGIN ID", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"LOGIN ID.1", "LOGIN ID.2"})

but the result for LOGIN ID.2 is null completely.

Presume it could be because username are all charaters and employee code are all digits?

Note : the Entire column is in Text Format.