Split column by data type

[batmanadk]

Hello PBI Community! I´m struggling to find the right way to split a column that has either numbers or text in each row. My objective is to have a column made entirely from numbers and the other one from text. I´ve tried the function ´Split Column By¨ in ¨Transform¨, by applying ¨By non-Digit to Digit¨ but it has only worked in columns where the digits where really small (1 digit only) or where cells had both text and number in it. My intention is to leave mixed rows (that have both text and numbers) in the text column. I´ll show what happenend in screenshots down below. 

I´ve thought about some kind of conditional column where if the row in one column has number then the new column would only have rows equal to that, but I don´t know if there´s that functionality.... I really appreciate the help and will be looking forward to hearing from you guys, since I have to present something on monday... Thanks a lot!

[v-juanli-msft]

Hi @batmanadk 

If any of the answers slove your problem,  could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

It is appreciated to share some typical sample data.

Best Regards

Maggie

[mahoneypat]

You could make two custom columns (one for numbers and one for text) with the Text.Select() function, as follows:

Number column

= Text.Select([TextColumn], {"0".."9"})

Text Column

= Text.Select([TextColumn], {"A".."Z", "a".."z"})

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[batmanadk]

Hey Pat, thanks a lot! It almost worked, the problem is that there are some mixed cells, and when that happens, I don't need the numbers getting pulled to the number column. By adding "0".."9"} to the new text column I was able to get the full column, but in the other one, it's pulling only the number (Where it shouldn't be pulling anything at all)

So this hapens

Original Text Column	New Number Column	New Text Column
Barra 1	1	Barra1

Thanks a lot for the help!

[artemus · Accepted solution]

The simple way of doing this is to:

1. Duplicate the column.

2. Then change the duplicated column to the number type

3. Replace errors with nulls

4. Then add a conditional column with if the duplicated column is null then the origional column, else null.

Using a custom column mentioned above with try would be:

= let parsed = try Number.From([Origional Text]) in [Number = parsed[Value]?, Text = if parsed[HasError] then [Origional Text] else null]

Then just expand the record column. If you want to avoid having to set the type of the column, then alter the Added column step to add a 3rd parameter: type [Number = number, Text = text]

[lbendlin]

What you also want to do is take advantage of the Power Query TRY ... OTHERWISE construct.

TRY to read the column as number OTHERWISE read it as text.

[Anonymous]

waiting for a better or more elegant solution, try this:

Table.AddColumn(Table.AddColumn(yourtab, "num", each if Value.Is([yourMixedCol], type number) then [yourMixedCol] else null), "txt", each if Value.Is([yourMixedCol], type text) then [yourMixedCol] else null)

[batmanadk]

Hello! Sorry for my ignorance, I´m kind of a noob here. what would I need to write where you wrote your tab? I´m in PowerQuery, so I´ve tried writing the table name, even though I knew that wasn´t it.... I have it like this :

Table.AddColumn(Table.AddColumn(yourtab, "num", each if Value.Is([Name], type number) then [Name] else null), "txt", each if Value.Is([Name], type text) then [Name] else null)

Thanks for your help!!

[Anonymous]

hi @batmanadk,

yourtab  in my statemnent is just the result variable of previous step: usully somethink like #"expanded ...".

Then you have only to find this identifier and put in place of yourtab.

good luck