Forum Discussion
Helper ISplit 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]
Microsoft EmployeeYou 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
Super UserWhat 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.