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
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.
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]
