Separate Numerical and Text into separate Columns

[Plump_Lil_Monk]

Hi

I have quite a large data set and am having an issue separating a particular column that is similar to the following:

123
153
Dep One
1896
DepThree
3265
758
Dep Two

I need to separate the column into two separate ones, one with text and one with numbers so that it looks like.

123	null
153	Dep One
null	null
1896	null
null	Dep Three
3265	null
758	null
null	Dep Two

is this possible? 

Thanks

[Syk · Accepted solution]

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. 

[tackytechtom · Accepted solution]

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/

[slorin · Accepted solution]

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

[tackytechtom · Accepted solution]

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/

[Syk · Accepted solution]

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.