Split 1 column (Num+Text & Numbers Only) into 2 columns

Hi there,

I would like to split the following tables which have 1 column with both Numbers+Text & Numbers Only,

Questions	Answer
Q1	5
Q2	1. Comments
Q3	2
Q4	Did this comments
Q5	No Feedback
Q6	8

Into 2 columns; 1 column with numbers only, the other should be Num+Text.

*Knowing that some of the inputs under "Opend-ended" will contain number as well.

Questions	Points	Open-ended
Q1	5	-
Q2	0	1. Comments
Q3	2	-
Q4	0	Did this comments
Q5	0	No Feedback

Thank you in advance.

Need Help

Icey

5 years ago

Hi rphang ,

First, in your expression, one more word was written.

Then, try amitchandak 's latest method to create two columns like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjRU0lEyVYrVATKNgExDPQXn/Nzc1LySYoigMVDQCMI0ATJdMlMUSjIyixWSUVSZAqX88hXcUlNTkhKTsyGCZkBBC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Questions = _t, Answer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Questions", type text}, {"Answer", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Points", each try Number.FromText([Answer]) otherwise 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Open-ended", each if [Points]=0 then [Answer] else "-"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Questions", type text}, {"Answer", type text}, {"Points", Int64.Type}, {"Open-ended", type text}})
in
    #"Changed Type1"