Forum Discussion
Split column by data type
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]
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
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!
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]