Separate Numerical and Text into separate Columns

First lets create a new column to help us determine if each row is a number or text.
Add column > Custom Column

try if Number.FromText([Column])-Number.FromText([Column])=0 then 1  else 0
otherwise 0

There are a few ways you could do this.. In this example, we cast the value in the original column to a number and subtract it from itself. Since the text cant be cast to a number type we just wrap it in the try... otherwise to catch all the errors! 

Then we just create a few conditional columns to split the original column.

 

Here's the M query if you're interested as well..

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorVAdKmENoltUDBPy8VImZhaQYTDMkoSoWIGhuZmYIZ5qYWcC0h5flKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"(blank)", "Column"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "NumOrText", each try if Number.FromText([Column])-Number.FromText([Column])=0 then 1  else 0
otherwise 0),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Number Column", each if [NumOrText] = 1 then [Column] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Text Column", each if [NumOrText] = 0 then [Column] else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Number Column", Int64.Type}})
in
    #"Changed Type1"

 Source is just the manually entered data. 

Hi Plump_Lil_Monk ,

 

How about this? 🙂

 

Before:

 

After:

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorVAdKmENoltUDBPy8VImZhaQYTDMkoSoWIGhuZmYIZ5qYWcC0h5flKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(#"Source", "column", "columnString"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"column", Int64.Type}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"column", null}}),
    #"Replace Values" = Table.ReplaceValue(#"Replaced Errors",each [columnString],each if [column] <> null then null else [columnString],Replacer.ReplaceValue,{"columnString"})

in
    #"Replace Values"

Hope this helps! 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Hi Plump_Lil_Monk 

just one step with Table.SplitColumn

= Table.SplitColumn(
      Your_Source,
      "Your_Column",
      each if Value.Type(_) = type number then {_, null} else {null, _},
      {"Your_Column.1", "Your_Column.2"}
  )

 Stéphane