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]